How can i make this Complex query for SQL database

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)) 

or

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..


Solution

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 
)
techinplanet staff

techinplanet staff


Windows 10 Kaufen Windows 10 Pro Office 2019 Kaufen Office 365 Lizenz Windows 10 Home Lizenz Office 2019 Home Business Kaufen windows office 365 satın al follower kaufen instagram follower kaufen porno