Home > Archive > PHP DB > July 2004 > Urgent JOIN help needed
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Urgent JOIN help needed
|
|
| Chris Payne 2004-07-31, 3:55 am |
| 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
 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
| |
| Kimberlee Jensen 2004-07-31, 3:55 pm |
| 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
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
|
|
|
|
|