3 table join

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
jwerre
Forum Newbie
Posts: 18
Joined: Thu Feb 05, 2009 5:06 pm

3 table join

Post by jwerre »

I'm trying to join three tables and having a little trouble with it. Here are my three tables:

Code: Select all

 
[u]tb_user[/u]
[u]user_id[/u]  | [u]name_first[/u] | [u]name_last[/u]
123456          | bill              | smith
987654          | john              | doe
    
[u]tb_game[/u]
[u]game_id[/u]  | [u]game_data[/u]  | [u]total_players[/u]  | [u]complete[/u]   | [u]start_date[/u]
0               | 71ce5...          | JSON_DATA 2           | 0                 | 2009-03-09 16:39:50
1               | c676c...          | JSON_DATA 1           | 0                 | 2009-03-09 17:39:50
 
[u]tb_match[/u]
[u]match_id[/u] | [u]game_id[/u]    | [u]user_id[/u]
0               | 71ce5...          | 123456
1               | 71ce5...          | 987654
2               | c676c...          | 123456
 
This is what I'm trying to get -- all the games with the user_id = 123456

Code: Select all

 
[u]match_id[/u] | [u]game_id[/u] |  [u]user_id[/u]  | [u]game_data[/u]  | [u]total_players[/u]  | [u]complete[/u]   | [u]start_date[/u]     | [u]name_first[/u] | [u]name_last[/u]
0               | 71ce5...       |  123456          | json_data         | 2                     | 0                 | 2009-03-09 16:39:50   | bill              | smith
2               | c676c...       |  123456          | json_data         | 1                     | 0                 | 2009-03-09 17:39:50   | bill              | smith
 
I join two tables just fine for some reason i can't get all three
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: 3 table join

Post by califdon »

If you're just looking for data from the Games table, for a particular User, you don't really need the 3rd table, so it could be as simple as:

Code: Select all

SELECT * FROM tb_game G LEFT JOIN tb_match M ON M.game_id=G.game_id WHERE M.user_id='123456'
but if you want to get data from the user table, as well, such as to show ALL players and the games data for their games, it gets a little more complex and probably will require GROUPing, but I'm running out of time, so I'll leave that for somebody else, if you indicate an interest in that.
Post Reply