dongzhuang6417 2015-01-18 04:47
浏览 97

Mysql表连接,​​但如果第一个表有信息,则返回值,但第二个不是

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.

  • 写回答

2条回答 默认 最新

  • dpdjv9559 2015-01-18 04:52
    关注

    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.

    评论

报告相同问题?

悬赏问题

  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥50 汇编语言除法溢出问题
  • ¥65 C++实现删除N个数据列表共有的元素
  • ¥15 Visual Studio问题
  • ¥15 state显示变量是字符串形式,但是仍然红色,无法引用,并显示类型不匹配
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波