CODEWITHSUNDEEP
phpmysql
user3304303
user3304303

Reputation: 1043

How to LEFT JOIN on mysql rows that don't exist past a certain year?

I have two tables, I'll list table names and relevant fields:

PLAYERS:
playerid

STATS:
statsid
playerid
year

Every time a player accumulated stats in a game, it put a row in the stats table and recorded the year.

Now, I would like get a list of all players in the players table who have NOT recorded any stats in 2016 and up.

I tried LEFT JOIN but don't really understand what I'm doing and couldn't get it to work. This is one of the things I tried, don't laugh

SELECT * FROM `players` LEFT JOIN `stats` ON players.playerid = stats.playerid where count(*) = 0 and stats.year >= 2016

Upvotes: 0

Views: 23

Answers (1)

hellowd
hellowd

Reputation: 315

It seems that you don't know the exists grammar, eg: 

select * 
from players p 
where not exists (select 1 
                  from stats s 
                  where s.playerid = p.playerid
                    and s.year >= 2016)

shows players who do not exist in the stats table for those years, easy to understand and remember.

Upvotes: 2

Related Questions