1

I have three tables in a mySQL database that I am querying with PHP:

players

player_id | player_name
-----------------------
1           Tom
2           Dick
3           Harry

games

game_id | game_name
-------------------
1         Tennis
2         Cricket
3         Rugby

gamePlayerRel

game_id | player_id
-------------------
1         2
1         3
2         3
3         2
3         3

I want a query that will return the players id and name as well as the games that they play. If they play no games - as in the case of Tom (1) - then I want the id and name and a null or 0 value.

Desired Result

player_id | player_name | game_id | game_name
---------------------------------------------
1           Tom           null      null
2           Dick          1         Tennis
2           Dick          3         Rugby
3           Harry         1         Tennis
3           Harry         2         Cricket
3           Harry         3         Rugby

The closest I can get is with the following query:

SELECT players.player_id, players.player_name, gamePlayerRel.game_id, games.game_name
FROM players
INNER JOIN gamePlayerRel
INNER JOIN games
ON players.player_id = gamePlayerRel.player_id
AND gamePlayerRel.game_id=games.game_id

This gets close except that it does not return data for players with games assigned to them. In the above examples the result is as I want except that Tom (1) with his null values is left out.

I'm a super noob at this so if I'm coming at it from the wrong angle please say so and if you can suggest a query that will do what I'm after you would make my day.

Thanks in advance for any help and advice.

Improve this question

3 Answers 3

Reset to default
5

To return the result set as you've shown you need to actually do two outer joins

SELECT players.player_id, players.player_name, gamePlayerRel.game_id, games.game_name
FROM players
    LEFT OUTER JOIN gamePlayerRel ON players.player_id = gamePlayerRel.player_id
    LEFT OUTER JOIN games ON gamePlayerRel.game_id=games.game_id

In your case this should work just fine, however, what I really think you're trying to do is an inner join between games and gamePlayerRel and then a Left Outer Join between that virtual table and the players table. You can accomplish that via something like this:

SELECT players.player_id, players.player_name, playerGames.game_id,      playerGames.game_name
FROM players
    LEFT OUTER JOIN (Select gameplayerRel.player_id, games.game_id, games.game_name  from games inner join gamePlayerRel on games.game_id = gamePlayerRel.game_id) playerGames
       on players.player_id = playerGames.player_id

In your case you'd never need this alternative syntax, but there are some times when things can get messed up because the two left outer joins way is not exactly logically equivalent.

Here is a SQL Fiddle showing the results of the queries

I know you're using mysql, but this article does a good job of explaining the nuances of the joins, but it is written for MSSQL, although it should apply to you just the same. http://weblogs.sqlteam.com/jeffs/archive/2007/10/11/mixing-inner-outer-joins-sql.aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

This should do the trick

SELECT players.player_id, players.player_name, gamePlayerRel.game_id, games.game_name
FROM players
    INNER JOIN gamePlayerRel ON players.player_id = gamePlayerRel.player_id
    LEFT OUTER JOIN games ON gamePlayerRel.game_id=games.game_id

You need to join each table individually and also need to use a LEFT OUTER JOIN for your games table. This will pull in at least one record for every match in the players/gamePlayerRel tables. If there isn't a match you'll be returned a NULL value.

Improve this answer

1 Comment

Cheers for this. I tried it and it was close but didn't return any data for the entry that was not linked to a game type. I have been prodding it since then and have finally got the result I was after. I'll post below in case anyone else is interested.
0

After being nudged in the right direction by the helpful folk of SO I have got a query that does what I am after though I am still not 100% sure why it works.

SELECT players.player_id, players.player_name, gamePlayerRel.game_id, games.game_name
FROM players
INNER JOIN gamePlayerRel ON players.player_id = gamePlayerRel.player_id
LEFT OUTER JOIN games ON gamePlayerRel.game_id=games.game_id

Thanks to those who commented. Once again this site proves one of the most useful I have ever found :)

Improve this answer

5 Comments

For anyone else struggling to get their head around joins - hopefully it's not just me - I have found this article quite helpful.
I believe your solution is the exact solution I posted.
Well now I'm embarrassed. I swear I tried yours and it didn't return any null values until I had played around with it for a while. It is very possible that I had a special moment and was checking the wrong code or something. I'll double check tomorrow when I'm back at work and then mark your answer as correct. Sorry for trying to steal your glory, it was entirely unintentional :)
The query you've posted doesn't return null values. See this sql fiddle sqlfiddle.com/#!8/da71e/3
Somewhere along the line I have got rather in a pickle with all this. The query I posted here is indeed the same one as was suggested by @MattBusche and doesn't return any null values. I think I copied the one from the answer to paste into phpMyAdmin and then pasted it back when I tried to answer my own question. Confusion ensued.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.