Page 1 of 1

Help with a query

Posted: Mon Oct 31, 2011 5:21 pm
by RCA86
Hi guys, I'm trying to write a query here, and I'm not really sure how to do it. It's a MySQL database, if that makes any difference.

There are three tables involved: products, categoryassociations, and images. What I need to do is get a table that shows the product information, and the image data for all the products that belong to category X.

So the relevant fields are:

| products |
--------------
productid
productname

| categoryassociations |
-----------------------------
categoryid
productid

| images |
------------
productid
imagefile

So the current query I'm working with (which doesn't work, but might help show you what I'm on about) is:

Code: Select all

SELECT p.productid,p.productname,pi.productid FROM `products` p 
INNER JOIN `images` pi ON p.productid = pi.productid
WHERE p.productid = (SELECT productid FROM categoryassociations ca WHERE ca.categoryid = 13)
I know this isn't quite right, but I don't know enough about SQL queries to work out how I can get the data for each category association row. :/

Any ideas?

Re: Help with a query

Posted: Mon Oct 31, 2011 8:10 pm
by twinedev
Try

[text]SELECT p.productid, p.productname, pi.imagefile
FROM products AS p
INNER JOIN images AS pi ON p.productid = pi.productid
INNER JOIN categoryassociations AS ca ON p.productid = ca.productid
WHERE ca.categoryid = 13[/text]

By the way, you were close to get it working with what you had without the second join, just needed an IN instead of = in the where...

[text]WHERE p.productid IN (SELECT .....[/text]

Re: Help with a query

Posted: Tue Nov 01, 2011 5:00 am
by RCA86
Ahhh you're a legend, works perfectly! :D

Thanks a million!