The New Tetris - "Lines Won" Queries

tnt_lines_won.sql

TNT Queries

-- select * from tntmatch t where not exists (select 'hi' from playermatch p where p.matchid = t.matchid);
-- select * from playermatch p where not exists (select 'hi' from tntmatch t where t.matchid = p.matchid);
-- select * from tntmatch t where (select count(matchid) from playermatch p where p.matchid = t.matchid) > 4;

--WRANK LINES WON (including own)

SELECT m.matchdate, p.username, pm.*, 
(select count(playerid) from playermatch where matchid = pm.matchid) as pCt, (select ifnull(sum(`lines`),0) from playermatch where matchid = pm.matchid and wrank >= pm.wrank)  as lines_won, (select group_concat(n.username) from playermatch b, player n where b.matchid = pm.matchid and n.playerid = b.playerid and b.playerid != pm.playerid) as opps
FROM playermatch pm, player p, tntmatch m WHERE pm.matchid = m.matchid AND p.playerid = pm.playerid AND (select count(playerid) from playermatch where matchid = pm.matchid) IN (4) ORDER BY lines_won DESC;

--ERANK LINES WON (including own)

SELECT m.matchdate, p.username, pm.*, 
(select count(playerid) from playermatch where matchid = pm.matchid) as pCt, (select ifnull(sum(`lines`),0) from playermatch where matchid = pm.matchid and erank >= pm.erank)  as lines_won FROM playermatch pm, player p, tntmatch m WHERE pm.matchid = m.matchid AND p.playerid = pm.playerid AND (select count(playerid) from playermatch where matchid = pm.matchid) IN (4) ORDER BY lines_won DESC;

--WRANK LINES WON (NOT including own)

SELECT m.matchdate, p.username, pm.*, (select count(playerid) from playermatch where matchid = pm.matchid) as pCt, (select ifnull(sum(`lines`),0) from playermatch where matchid = pm.matchid AND playerid != pm.playerid and wrank > pm.wrank)  as won, (select group_concat(n.username) from playermatch b, player n where b.matchid = pm.matchid and n.playerid = b.playerid and b.playerid != pm.playerid) as opps FROM playermatch pm, player p, tntmatch m WHERE pm.matchid = m.matchid AND p.playerid = pm.playerid AND (select count(playerid) from playermatch where matchid = pm.matchid) IN (4) ORDER BY lines_won DESC;

--ERANK LINES WON (NOT including own)

SELECT m.matchdate, p.username, pm.*, (select count(playerid) from playermatch where matchid = pm.matchid) as pCt, (select ifnull(sum(`lines`),0) from playermatch where matchid = pm.matchid AND playerid != pm.playerid and erank > pm.erank)  as lines_won FROM playermatch pm, player p, tntmatch m WHERE pm.matchid = m.matchid AND p.playerid = pm.playerid AND (select count(playerid) from playermatch where matchid = pm.matchid) IN (4) ORDER BY lines_won DESC;