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?