Subqueries

I have four tables (Customer, OrderTbl, OrdLine and Product). I have to list the customer number of customers who have ordered all of the products that a manufacture called ColorCorp have to offer.

describe customer;
CUSTNO

describe product;
PRODNO
PRODMANUFACTURE

describe ordertbl
ORDNO
CUSTNO

describe ordline
ORDNO
PRODNO

I tried very unsuccessfully using subqueries. I just can’t manage to get it going. Can anyone please give me any adivice or help, very much appreciated.

next time provide some ddl.
try this:

create table customer (custno int)
create table product (prodno int, prodmanufacture varchar(30))
create table ordertbl (ordno int, custno int)
create table ordline (ordno int, prodno int)

insert into customer values(1)
insert into customer values(2)

insert into product values(1, 'colorcorp')
insert into product values(2, 'colorcorp')
insert into product values(3, 'corp1')
insert into product values(4, 'corp2')

insert into ordertbl values(1, 1)
insert into ordertbl values(2, 2)

insert into ordline values(1, 1)
insert into ordline values(1, 2)
insert into ordline values(2, 3)


select * from customer cst where not exists
(select * from product p cross join customer c
left join ordertbl ot on ot.custno = c.custno
left join ordline o on o.ordno = ot.ordno and o.prodno = p.prodno
where c.custno = cst.custno and p.prodmanufacture = 'colorcorp' and o.prodno is null)

hth,
Cristian Babu

thanks again for your help,

it came up with an error:

PHP Code:

---

SQL> select * from customer cst where not exists
  2  (select * from product p cross join customer c
  3  left join ordertbl ot on ot.custno = c.custno
  4  left join ordline o on o.ordno = ot.ordno and o.prodno = p.prodno
  5  where c.custno = cst.custno and p.prodmanufacture = 'colorcorp' and o.prodno is null)
  6  ;
(select * from product p cross join customer c
                         *
ERROR at line 2:
ORA-00907: missing right parenthesis 


---

i tried to fix it by putting a bracket after the 'customer' on line 2 but it didn't work, do you have any ideas?

it seems like your tool splits the query after the second line. try to run it in ms sql server query analyzer, or make a single line of it and rerun in your tool.

hth,
Cristian Babu