record not in table 2

[RaceAap]

Hi,

I need to select only those records of table1 that are not in table2.
In both table's there is a field called: order_id to determine this, but as a sql newbie i can't figure out how to do this.

Hopefully you guru's can help me.

Thanx
Lon

 

[jimbopalmer]

Select order_id from Table1
minus
Select order_id from Table2;

if you want more fields, you may wish to make this a sub-query

Select * from table1 where order_id in
(Select order_id from Table1
minus
Select order_id from Table2) I tried to remain child-like, all I acheived was childish.

 

[karluk]

I'm afraid that the "minus" operator is an Oracle specific solution. It doesn't work in other databases, in particular SQL Server.

I think a more general solution would be something like

Select order_id from Table1
where order_id not in
(select order_id from Table2)

 

[RaceAap]

thanx,

I found another sollution for problem somewhere else on the web

SELECT o.* from tbl_order o LEFT JOIN tbl_lineitem i ON o.order_id = i.order_id where i.order_id IS NULL

and this ons works fine for me.

Lon