More Join Problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

More Join Problems

Post by Steveo31 »

Still not 100% sure on how JOINS work, so it's a lot of guess and check goin on here. A lot of the documentation talks about foreign key relationships, and keys are another thing I'm a little cloudy on.

Anyway, here's what I'm trying to execute:

Code: Select all

SELECT u.username, p.product_id, up.thumb_path FROM users AS u, uploads AS up INNER JOIN products AS p ON up.product_id = p.product_id
And it's returning:

Code: Select all

CanadaMan102  	RnVybml0dXJl-TW92YWJsZQ==-0  	members/steveo31/microwave_thumb.jpg
steveo31 	RnVybml0dXJl-TW92YWJsZQ==-0 	members/steveo31/microwave_thumb.jpg
CanadaMan102 	VG9vbHM=-VG9vbHM=-1 	members/steveo31/tools_thumb.jpg
steveo31 	VG9vbHM=-VG9vbHM=-1 	members/steveo31/tools_thumb.jpg
CanadaMan102 	SGFyZHdhcmU=-RWxlY3RyaWNhbA==-2 	members/steveo31/blender_thumb.jpg
steveo31 	SGFyZHdhcmU=-RWxlY3RyaWNhbA==-2 	members/steveo31/blender_thumb.jpg
CanadaMan102 	QXV0b21vYmlsZXM=-Q2Fycw==-3 	members/steveo31/modelt_thumb.jpg
steveo31 	QXV0b21vYmlsZXM=-Q2Fycw==-3 	members/steveo31/modelt_thumb.jpg
CanadaMan102 	SGFyZHdhcmU=-RWxlY3RyaWNhbA==-4 	members/steveo31/toaster_thumb.jpg
steveo31 	SGFyZHdhcmU=-RWxlY3RyaWNhbA==-4 	members/steveo31/toaster_thumb.jpg
CanadaMan102 	VG9vbHM=-VG9vbHM=-5 	members/steveo31/wrech_thumb.jpg
steveo31 	VG9vbHM=-VG9vbHM=-5 	members/steveo31/wrech_thumb.jpg
Whereas it should return:

Code: Select all

steveo31 	RnVybml0dXJl-TW92YWJsZQ==-0 	members/steveo31/microwave_thumb.jpg
steveo31 	VG9vbHM=-VG9vbHM=-1 	members/steveo31/tools_thumb.jpg
CanadaMan102 	SGFyZHdhcmU=-RWxlY3RyaWNhbA==-2 	members/CanadaMan102/blender_thumb.jpg
steveo31 	QXV0b21vYmlsZXM=-Q2Fycw==-3 	members/steveo31/modelt_thumb.jpg
steveo31 	SGFyZHdhcmU=-RWxlY3RyaWNhbA==-4 	members/steveo31/toaster_thumb.jpg
CanadaMan102 	VG9vbHM=-VG9vbHM=-5 	members/CanadaMan102/wrech_thumb.jpg

Hope this isn't too much. :)
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post by swdev »

Looks to me like you are only JOINing the 'uploads' and 'products' tables. There is no JOINing with the 'users' table, so what SQL does is to return the results from JOINing the 'uploads' and the 'products' tables for each row in the 'users' table.

So, to solve your problem, simply add a join between the 'users' table and either the 'uploads' or 'products' table, whichever one has the linking value.

Hope this helps
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Code: Select all

SELECT u.username, p.product_id, up.thumb_path FROM users AS u, uploads AS up INNER JOIN products AS p ON up.product_id = p.product_id
would be far neater (and easier to debug) as:

Code: Select all

SELECT users.username,products.product_id,uploads.thumb_path
FROM users,uploads,products
WHERE uploads.product_id=products.product_id AND
              users.user_id=uploads.user_id
including a guess for the code change (last line) as suggested in previous post.

In general I find it useful to only rename tables within a select when absolutely necessary. Also INNER JOIN is not required as it is defaulted (at least in all the DB's I have experienced).

I also like to have product_id in the product table and upload.product_ref to distinguish between them, although this is only preference I often find it useful for readability.
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

Awesome guys, thanks for the explanations. One step closer to gettin it :D
Post Reply