![]() ![]() 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.We’re not going to explain the whole query, but only the JOIN part. Here is a SQL Fiddle showing the results of the queries 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. LEFT OUTER JOIN (Select ayer_id, games.game_id, games.game_name from games inner join gamePlayerRel on games.game_id = gamePlayerRel.game_id) playerGames You can accomplish that via something like this: SELECT ayer_id, ayer_name, playerGames.game_id, playerGames.game_name 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. LEFT OUTER JOIN games ON gamePlayerRel.game_id=games.game_id ![]() LEFT OUTER JOIN gamePlayerRel ON ayer_id = ayer_id ![]() To return the result set as you've shown you need to actually do two outer joins SELECT ayer_id, ayer_name, gamePlayerRel.game_id, games.game_name Thanks in advance for any help and advice. 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. In the above examples the result is as I want except that Tom (1) with his null values is left out. ![]() This gets close except that it does not return data for players with games assigned to them. The closest I can get is with the following query: SELECT ayer_id, ayer_name, gamePlayerRel.game_id, games.game_name 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.ĭesired Result player_id | player_name | game_id | game_name I want a query that will return the players id and name as well as the games that they play. I have three tables in a mySQL database that I am querying with PHP: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |