CODEWITHSUNDEEP
sql-serverselectsql-delete
user1404954
user1404954

Reputation: 35

Select rows from table1 which don't have rows in table2

I have 2 tables, OrderTable & OrderDetailTable.

I am trying to select rows from OrderTable that don't have any rows in OrderDetailTable so we can Delete them.

Upvotes: 0

Views: 111

Answers (3)

Panos Bariamis
Panos Bariamis

Reputation: 4653

Suppose that OrderTable has a column id, and OrderDetailTable has a column orderTable_id

select * from OrderTable
WHERE id not in (
 select ot.id from OrderTable ot inner join OrderDetailTable odt on odt.orderTable_id = ot.id
)

Upvotes: 0

Pablo
Pablo

Reputation: 3673

SELECT o.*
FROM OrderTable o
LEFT JOIN OrderDetailTable od ON od.idOrderTable = o.id
WHERE od.id IS NULL;

od.id can be any field from OrderDetailTable that can't be null.

Upvotes: 0

juergen d
juergen d

Reputation: 204756

I assume you have an id relation between the 2 tables:

select * from OrderTable  
where orderdetails_id not in (select id from OrderDetailTable)

and to delete them 

delete from OrderTable  
where orderdetails_id not in (select id from OrderDetailTable)

Upvotes: 2

Related Questions