Asked By: Anonymous
I have a database that looks like this:
Player (UserId, Mail, Password, Username, LastLogin)
Leaderboard (UserId*, Level, Kills, Deaths)
Match (MatchId, HostId*, ServerIp, StartTime, Team1Points, Team2Points)
MatchStats (UserId*, MatchId*, Kills, Deaths)
Weapons (IdWeapon, Name, Damage, FireRate, ReloadTime, Range)
Inventory(UserId*, WeaponType*, Skin)
I need a query that selects the LastLogin of every Player that is dead more than 12 times (MatchStats.Deaths > 12) in at least 3 matches.
Can someone help me? I’m having troubles with possibles Joins or Subqueries.
I tried something like:
SELECT LastLogin FROM Player WHERE UserId=(SELECT UserId FROM (SELECT * FROM MatchStats WHERE Deaths>12 AS TAB) WHERE COUNT(UserId)>3))
SELECT LastLogin FROM Player WHERE (SELECT COUNT(SELECT Count(*) FROM MatchStats WHERE Deaths>12 GROUP BY UserId)>3 GROUP BY UserId;
But i feel i’m quite far from having the corret query, maybe I’m missing a Join or maybe I’m doing it totally wrong..
Answered By: Anonymous
You can use
exists to check whether a player have 12 death in at least three matches
select lastlogin from Player p where exists ( select UserIdfrom MatchStats m where deaths>=12 and p.UserId=m.UserId group by userid having count(distinct matchid)>=3 )