Select records from a table where two columns are not present in another table

Question

I have Table1:

Id      Program Price   Age
12345   ABC     10      1
12345   CDE     23      3
12345   FGH     43      2
12346   ABC     5       4
12346   CDE     2       5
12367   CDE     10      6

and a Table2:

ID      Program BestBefore
12345   ABC     2
12345   FGH     3
12346   ABC     1

I want to get the following Table,

Id      Program  Price  Age
12345   CDE      10     1
12346   CDE      2      5
12367   CDE      10     6

I.e get the rows from the first table where the ID+Program is not in second table. I am using MS SQL Server express 2012 and I don't want to add any columns to the original databases. Is it possible to do without creating temporary variables?

Answer 1

Several ways to do this, here's one using not exists:

select *
from table1 t1
where not exists (
    select 1
    from table2 t2 
    where t1.id = t2.id and t1.program = t2.program
)