CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
doe
doe

Reputation: 148

ignore records that exists in two tables sql server

table 1

ID    Department     Category
555       16           test
888       16           test
0001      16           test

table 2

ID  Department     Date
555    67         2015-04-28
111    58         2015-04-28
000    45         2015-04-28

how do i create a stored procedure if i had to pass in the parameter value table1.department=16 it must retreive all the records from table1 but if the id is in table 2 it must ignore that record.

expected output if i pass parameter table1.department=16

output

ID    DEpartment       Category
888     16               test
0001    16               test

id 555 should get ignored.

what was attempted

select *
from table1 as t1 inner join table2 as t2 on t1.ID=t2.ID
where t1.department='16'

Upvotes: 3

Views: 175

Answers (1)

Chiragkumar Thakar
Chiragkumar Thakar

Reputation: 3716

This query will return your desired Result,

SELECT Table1.ID, 
        Table1.Dept,
        Table1.Category 
FROM table1 WHERE Table1.ID NOT IN (SELECT Table2.ID FROM table2)

Upvotes: 2

Related Questions