NOT EXISTS problem

steve_o · #1 (**permalink**) 09-26-05, 10:43

I have a table in which there are the following 2 columns: sitm and pitm.
sitm is the child item, and pitm is the parent item. i would like to find all child items that are not parent items.

I would have thought that the following query would do this:

Code:

select sitm from lhpms010
where not exists (select pitm from lhpms010)

Anybody have any suggestions why this doesn't work? There are certainly items in the sitm column that are not in the pitm column...

steven.

Littlefoot · #2 (**permalink**) 09-26-05, 10:45

How about

SELECT sitm FROM lhpms010
MINUS
SELECT pitm FROM lhpms010;

urquel · #3 (**permalink**) 09-26-05, 15:44

If your DBMS does not support the minus, then you could try a correlated subquery with your not exists predicate.

Code:

select sitm from lhpms010 a
where not exists (select pitm from lhpms010 b
                         where b.pitm = a.sitm)

r937 · #4 (**permalink**) 09-26-05, 23:37

there were two additional really good alternative approaches posted into the exact same thread as this one in the sql server forum

<sigh />

Nirmala · #5 (**permalink**) 10-04-05, 09:51

Hi ,

The MINUS query should work.

r937 · #6 (**permalink**) 10-04-05, 10:03

Quote:

Originally Posted by Nirmala

The MINUS query should work.

but MINUS is proprietary, and this is the non-proprietary SQL forum

Peter.Vanroose · #7 (**permalink**) 10-04-05, 10:57

Try replacing MINUS by EXCEPT; that's standard SQL.

Peter.Vanroose · #8 (**permalink**) 10-04-05, 11:02

Quote:

Originally Posted by steve_o

select sitm from lhpms010
where not exists (select pitm from lhpms010)

Typically, in an EXISTS, the selected value (here pitm) is irrelevant.
So what you wanted was a WHERE condition in your subquery, specifying that the sitm of the outer query appears in the pitm column of the subquery:

Code:

select sitm from lhpms010 AS x
where not exists (select 1 from lhpms010 where pitm = x.sitm)

Internet Services

Web Development

Digital Marketing

Consumer Tech