Re: Advanced SQL question: NOT EXISTS

Jim,

The meaning of the query you have written is: Find the salesperson such that every order is associated with that salesperson and with some customer.

To find the salesperson who has an order with every customer use this:

select salesperson from salesperson
where not exists
 (select * from customer
  where not exists
  (select * from order
   where order.salesperson = salesperson.salesperson    and order.customer = customer.customer))

This exact example with different table and column names can be found in C.J. Date's "Relational Database Writings 1989-1991", Chapter 9, "Relational Calculus as an Aid to Effective Query Formulation. It can also be found in C.J Date and Colin J. White's "A Guide to DB2."
(both by Addison-Wesley)

Tom Jamieson pp003009_at_interramp.com

In article <3psubg$or6_at_newsbf02.news.aol.com>, jamesf5088_at_aol.com (JamesF5088) says:
>
>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
> )
> );
>
>
>Thank you in advance for all replies to this question.
>Jim
>
  Received on Sun Jun 04 1995 - 00:00:00 CEST