CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Morpheus
Morpheus

Reputation: 3523

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

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?

Upvotes: 12

Views: 12838

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

One possible variant is to use LEFT JOIN:

SELECT
    Table1.*
FROM
    Table1
    LEFT JOIN Table2
        ON  Table1.ID = Table2.ID
        AND Table1.Program = Table2.Program
WHERE
    Table2.ID IS NULL

Upvotes: 7

sgeddes
sgeddes

Reputation: 62831

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
)

Upvotes: 22

Related Questions