CODEWITHSUNDEEP
phpmysql
crashkid
crashkid

Reputation: 55

Mysql table join, but return values if first table has information, but second doesnt

I have the following mysql query

        SELECT Name, Summoner_Name, ROUND(SUM(timePlayed)/60) as Total_Time 
                    FROM UserNames, games_database 
                    WHERE (UserNames.ID = games_database.UserNames_ID AND 
             UserNames.ID IN ({$Member_Ids_Sql}))
                    GROUP BY UserNames.ID 
                    ORDER BY Total_Time DESC;

It effectively will grab the players name, summoner names, and total play_time. It does this by using a table join. As one table Usernames, contains the users ID, Name and Summoner Name. Where as the Games_database table holds every game the player has played.

What I want to do is display the information of users that are in the UserNames table, but haven't played any games yet.

Extra Information: UserNames Database contains ID, Summoner_ID, Summoner_Name, Name

Games_database Database contains

ID, Match_ID, my_Date, timePlayed, champion, win, Summoner_ID, UserNames_ID, Game_Type

I got this working perfectly for all users with games, but when a new user enters the system, they aren't shown in this query due to no games being played.

Upvotes: 0

Views: 44

Answers (2)

Jeremy Farrell
Jeremy Farrell

Reputation: 1491

What you need here is a left outer join. A left outer join will return all results in the first table (filleted by your where) with the results in the second table. If there are no matches a null will be shown.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You want a left join to find non-matches:

SELECT Name, Summoner_Name, ROUND(SUM(timePlayed)/60) as Total_Time 
FROM UserNames LEFT JOIN
      games_database 
      ON UserNames.ID = games_database.UserNames_ID 
WHERE UserNames.ID IN ({$Member_Ids_Sql}) AND
      games_database.UserNames_ID is null
GROUP BY UserNames.ID 
ORDER BY Total_Time DESC;

Note: you should learn to always use explicit join syntax.

Upvotes: 0

Related Questions