Help with a query
Posted: Mon Oct 31, 2011 5:21 pm
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:
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?
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)
Any ideas?