Trying to understand multiple table queries...

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
haptic
Forum Newbie
Posts: 11
Joined: Fri Sep 08, 2006 7:21 pm

Trying to understand multiple table queries...

Post 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.
vadim88
Forum Newbie
Posts: 3
Joined: Mon Feb 04, 2008 9:40 am

Re: Trying to understand multiple table queries...

Post 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.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Trying to understand multiple table queries...

Post 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.
Post Reply