Page 1 of 1

More Join Problems

Posted: Wed Nov 03, 2004 12:33 am
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. :)

Posted: Wed Nov 03, 2004 8:59 am
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

Posted: Wed Nov 03, 2004 9:20 am
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.

Posted: Sat Nov 06, 2004 5:44 pm
by Steveo31
Awesome guys, thanks for the explanations. One step closer to gettin it :D