Page 1 of 1

Trying to understand multiple table queries...

Posted: Mon Feb 04, 2008 11:33 am
by haptic
Hi, I am trying to compare three tables data with a single query. But one of the conditions doesn't seem to matter in the results.

My query is:

SELECT content.subcategories_id, titles.title, media.location, media.text
FROM content, media, titles
WHERE content.subcategories_id=7
AND content.type=2
AND titles.id=media.titles_id
GROUP BY media.id
ORDER BY media.id

Content is a table that holds ids to the requested media content in relation to the subcategory id, Media has information regarding the media file, Titles has the headline title for the media.

The problem seems to be that content.subcategories_id=7 doesn't appear to matter. It shows the results for all subcategories with media content.
Any help is very appreciated.

Re: Trying to understand multiple table queries...

Posted: Mon Feb 04, 2008 12:15 pm
by vadim88
Dont know if i got u right but i would try to use this, lets assume that: content, media, titles are the tables and:
subcategories_id, title, location, text, type, id, titles_id are the fields.
i would do something like this:

Code: Select all

SELECT c.*, m.*, t.*
FROM content c, media m, titles t 
WHERE c.subcategories_id=7 
AND c.type=2 
AND t.id=m.titles_id 
GROUP BY m.id
ORDER BY m.id
try that, and in tables media and titles, u have a column name id in both of them, i think u should rename it (if u can) to mid and tid so there would be no complications later on.

Re: Trying to understand multiple table queries...

Posted: Mon Feb 04, 2008 12:27 pm
by Zoxive

Code: Select all

SELECT c.subcategories_id, t.title, m.location, m.text 
FROM content c
WHERE c.subcategories_id = 7 AND c.type = 2
LEFT JOIN titles t ON # Need TO Associate titles TO content some how; OR media TO content then titles TO media
LEFT JOIN media m ON m.titles_id = t.id
GROUP BY m.id
Maybe some joins?

Also, you aren't connecting to the titles table anyway, and also with media, because you are trying to connect media to titles, but titles was never connected.