CODEWITHSUNDEEP
sqlsql-servert-sql
Elham Azadfar
Elham Azadfar

Reputation: 737

How to select all records from one table that do not exist in particular year?

the table looks like this

num  Year
 1 | 2014
 2 | 2014
 3 | 2014
 2 | 2015
 4 | 2015
 5 | 2015
 6 | 2015

I would like my query to return 

 4 | 2014
 5 | 2014
 6 | 2014
 1 | 2015
 3 | 2015

from 1 to 6, the number that is not used in particular year.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

Generate the all the combinations and then take out the ones that exist:

select n.num, y.year
from (select distinct num from t) n cross join
     (select distinct year from t) y left join
     t
     on t.num = n.num and t.year = y.year
where t.num is null;

Note that year is a bad name for a column in SQL Server because it is the name of a function and a keyword (think datepart()).

Upvotes: 2

Related Questions