Joins !

[PANTLORD]

Hi there,

Have two tables A & B joined on key called system_key they hold several cols each. I want to find any orphan records in A i.e. where the link is dead or unbroken I'd like to identify these recs for possible deletion before srchiving. I can get the result I want using a minus statement i.e.

select system_key from tableA
minus
select system_key from tableB;

however I feel this qry coud be better by using joins trouble is I can't get them to work, here's what I have;

SELECT A.system_key
FROM tableA A, tableB B
WHERE A.SYSTEM_KEY(+)= B.SYSTEM_KEY
AND B.SYSTEM_KEY IS NULL;

it just seems to return columns of zero's though, any ideas how I can write this using a join ?

Many thanks,
Mully

 

[PANTLORD]

OK thnk I've solved my own issue here !

select A.*
from tableA A, tableB B
where A.system_key = B.system_key (+)
and B.system_key is null;

Cheers anyhows !

 

[amrita418]

For the sake of completeness, code using ANSI join syntax which oracle supports from 9i up:

SELECT    A.*
FROM      TableA A
          LEFT OUTER JOIN TableB B
          ON (A.System_Key = B.System_Key)
WHERE     B.System_Key IS NULL

Regards,
AA

 

[PANTLORD]

Tried this originally but I'm currently using ORACLE 8i and I don't think it supports it fully.

Thanks,
Mully

 

[GeppoDarkson]

Select * From A
Where Not Exists(Select * From B Where B.system_key=A.system_key)

Ciao,
GeppoDarkson.