Re: Advanced SQL question: NOT EXISTS
From: <surya_at_usa.net>
Date: 1995/05/23
Message-ID: <3ptjc5$3mp_at_earth.usa.net>#1/1
:> ORDER
:> David, Customer1
:> David, Customer2
:> Susan, Customer1
:>
:>
:> I've tried the following SELECT statement with no success.
:>
:> select salesperson
:> from salesperson
;> where not exists
:> ( select *
:> from order
:> where not exists
:> ( select * from customer
:> where customer.customer = order.customer
:> and salesperson.salesperson = order.salesperson
:> )
:> );
:>
DELETED
>>>>
How about:
Date: 1995/05/23
Message-ID: <3ptjc5$3mp_at_earth.usa.net>#1/1
> jamesf5088_at_aol.com (JamesF5088) writes:
:> From the following tables I would like to find the SALESPERSON who has an
:> ORDER with every CUSTOMER ( David in the example) using NOT EXISTS.
:>
:> Tables:
:> SALESPERSON
:> salesperson char(10)
:>
:> ORDER
:> salesperson char(10)
:> customer char(10)
:>
:> CUSTOMER
;> customer char(10)
:>
:> The tables are poplulated as follows:
;> SALESPERSON
:> David
;> Susan:>
:> Robert
:> ORDER
:> David, Customer1
:> David, Customer2
:> Susan, Customer1
:>
;> CUSTOMER ;> Customer1
:> Customer2
;>
:>
:> I've tried the following SELECT statement with no success.
:>
:> select salesperson
:> from salesperson
;> where not exists
:> ( select *
:> from order
:> where not exists
:> ( select * from customer
:> where customer.customer = order.customer
:> and salesperson.salesperson = order.salesperson
:> )
:> );
:>
DELETED
>>>>
How about:
select salesperson from salesperons s
where not exists (select customer from customers c
minus select customer from orders o where o.salesperson = s.salesperson);
Ganesh Puranik
Sr. Consultant,
Surya Systems, Inc.
Received on Tue May 23 1995 - 00:00:00 CEST