Joining three tables using left and normal join

mohtasham1983 · 07-23-2008, 07:51 PM

Hi,

I'm trying to join three tables using a left join and a normal join. I know it is possible to achieve that using php by executing several queries, but I don't want this process be that expensive, so I'm trying to do the whole thing in one query.

Here is the useful information about my database schema:

table 1: matchResult
columns: match_id,team1,team2,status

table 2: teams
columns: team_id,team_name

table 3: predicts
columns: match_id,user_id

What I need is to return a list of matches whose status equals to two and join records from predicts table based on match_id and those whose user_id either equals to 2 or is null.

To achieve that I use the following query:

Code:

select matchResult.match_id,matchResult.team1,matchResult.team2,predicts.user_id from 
matchResult LEFT join predicts using (match_id)
 where matchResult.status=2 and (predicts.user_id=2 || predicts.user_id IS NULL);

This query returns the following information:

PHP Code:



+----------+-------+-------+---------+
| match_id | team1 | team2 | user_id |
+----------+-------+-------+---------+
|        5 |    92 |    94 |       2 | 
|        7 |   104 |    98 |    NULL | 
|        8 |   105 |   107 |    NULL | 
|        9 |   109 |    96 |    NULL | 
|       10 |   111 |   103 |    NULL | 
|       11 |    95 |    97 |    NULL | 
|       12 |   101 |   110 |    NULL | 
|       13 |    93 |   108 |    NULL | 
+----------+-------+-------+---------+
8 rows in set (0.00 sec)

So far everything looks good, but I need to output the team names with their along side team IDs.

As you can see, the teams table contains a list of teams and their corresponding IDs. matchResult table has tow foreign keys (team1 and team2) who refer to the team_id of teams table.

I would like to know how I can join teams table to the existing query so that the output looks something like this:

PHP Code:



+----------+-------+-------+---------+-----------+-----------+
| match_id | team1 | team2 | user_id |  team1    |    team2  |
+----------+-------+-------+---------+-----------+-----------+
|        5 |    92 |    94 |       2 | Man U     |  Chelsea  |
|        7 |   104 |    98 |    NULL | Arsenal   |  Liverpool|
|        8 |   105 |   107 |    NULL | 
|        9 |   109 |    96 |    NULL | and so on
|       10 |   111 |   103 |    NULL | 
|       11 |    95 |    97 |    NULL | 
|       12 |   101 |   110 |    NULL | 
|       13 |    93 |   108 |    NULL | 
+----------+-------+-------+---------+
8 rows in set (0.00 sec)

I have never had experience of executing such complex queries, so I would be glad if you help me with that.

Thanks

jlinkels · 07-23-2008, 09:49 PM

Usually I try my queries by successive approximation until they work, so it is a bit difficult without the actual tables. Let me try, double check for silly syntax errors.

Code:

SELECT matchResult.match_id, team1, team2, user_id, teams1.team_name as tn1, teams2.team_name as tn2 from 
matchResult LEFT JOIN predicts ON matchResult.match_id = predicts.match_id
LEFT JOIN teams as teams1 on teams1.team_id = matchResult.team1
LEFT JOIN teams as teams2 on teams2.team_id = matchResult.team2
WHERE matchResult.status=2 AND (predicts.user_id=2 || ISNULL (predicts.user_id));

The trick is in selecting TWO records from teams, one matching matchResult.team1 and the other one as matchResult.team2. Since you cannot select a record from the same table using two different ON clauses, you alias the table (teams as teams2) and you use two aliases for the two ON clauses.

The other trick is teams1.team_name as tn1 where you alias the column. Both colums must have a different name otherwise you cannot distiguish them in your PHP array.

teams1.team_name refers to the alias you create in the JOIN clause.

If it doesn't work, try to replace the LEFT JOIN with plain JOIN here and there

If it still doesn't work let me know, I would have to create the tables to try further. But it is certainly possible, I do it all the time. (Iteratively, that is!)

Just a hint, rename your matchResult.team1 to matchResult.team1_id. It is an id actually, and it is easier remind you of that.

jlinkels

mohtasham1983 · 07-23-2008, 10:05 PM

Excellent help. You taught me a new trick. By the way, you guessed right that I needed to create alias for team. since I was generating an xml document based on column names of the database by using mysql_fetch_assoc, i was having trouble at first. But your trick took care of everything.

Thank you very much.