Help with a query

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
RCA86
Forum Commoner
Posts: 32
Joined: Thu Mar 10, 2011 1:03 pm

Help with a query

Post 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?
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Help with a query

Post 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]
RCA86
Forum Commoner
Posts: 32
Joined: Thu Mar 10, 2011 1:03 pm

Re: Help with a query

Post by RCA86 »

Ahhh you're a legend, works perfectly! :D

Thanks a million!
Post Reply