RE: Urgent JOIN help needed

Why you are getting multiple results is because a JOIN attempts to find all the possible combinations in the output. (It's called a Cartesian product, not that you care at this very moment.) Also, when doing joins, it's a bad idea to do a select * since you will have two fields with the same value (the field on which you are joining. Also, it's best to use the . notation for table reference so that you have no abiguity. This is one picky note, but not all dbs are as flexible as MySQL: when you are specifying the ON clause, your LEFT table should always be the table to the LEFT of the equal sign. MySQL does not care, but it's good practice. I would change it to: $sql = "SELECT vendorprices.*, fooditems.Description FROM vendorprices LEFT JOIN fooditems on (vendorprices.VendorNumber = fooditems.CategoryNumber) WHERE (vendorprices.CategoryNumber = '$VendorID'). You are on the right track with LEFT Join, that is precisely what is needed for the output you want. -----Original Message----- From: Chris Payne [mailto:chris_payne@planetoxygene.com] Sent: Fri 7/30/2004 8:25 PM To: php-db@lists.php.net Cc: Subject: Urgent JOIN help needed Hi there everyone, I'm new to JOINS and have followed some info in the MySQL manual but I'm at a loss, using the code I'll paste below, I get each result 4 times and I am confused as to why? Basically I'm trying to display ALL fields from the vendorprices table, and grab just the Description column from the fooditems table, the factor which joins them both is the string $VendorID which in the vendorprices table is VendorNumber and in the fooditems table is CategoryNumber. $sql = "SELECT * FROM vendorprices LEFT JOIN fooditems on (vendorprices.VendorNumber = fooditems.CategoryNumber AND fooditems.CategoryNumber = '$VendorID') WHERE vendorprices.VendorNumber='$VendorID' AND fooditems.CategoryNumber='$VendorID'"; Can anyone see where I'm going wrong? This is driving me nuts and I need to figure it out urgently. Thank you for your help. Regards Chris