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:[EMAIL PROTECTED]
Sent:   Fri 7/30/2004 8:25 PM
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


$sql = "SELECT * FROM vendorprices LEFT JOIN fooditems on
(vendorprices.VendorNumber = fooditems.CategoryNumber AND
fooditems.CategoryNumber = '$VendorID') WHERE
vendorprices.VendorNumber='$VendorID' AND


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.




Reply via email to