Joining 3 tables

[ag1060]

Hello,

I'm trying to join three tables with mysql and php. So far, I have this:

select username,psummary
FROM subscribers join users_db
ON subscribers.uid = users_db.uid 
join projects
ON users_db.uid = projects.uid
AND subscribers.sub_user='1'

When I run the code, it only returns one result, instead of all results....any idea?

Thanks in advance

 

[r937]

any idea?

yeah, there's only one joined result row that meets those criteria



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Olavxxx]

Hi, maybe the problem is that you are missing data in some of the other tables?

You can check this with:

SELECT count(*) FROM subscribers WHERE subscribers.uid NOT IN (SELECT uid FROM users_db) AND subscribers.sub_user='1' 
SELECT count(*) FROM subscribers WHERE subscribers.uid NOT IN (SELECT uid FROM projects) AND subscribers.sub_user='1' 
SELECT count(*) FROM subscribers WHERE subscribers.uid NOT IN (SELECT uid FROM users_db) AND subscribers.sub_user='1'

ps. It's still morning here in Norway, so they might produce some errors

If only one of them returns 1 row, it means that you only have one row in one of the other tables that match your criteria. With natural joins, you will only see what get's a match in the other joined tables. If you wish to show subscribers that are not in projects or in users_db, you have to have outer joins

However, should your users_db be the "inner" table in the outer join? (As I believe they have to be users to be in a project or be a subscriber?).

Olav Alexander Mjelde