Results 1 to 4 of 4

Thread: Subqueries

  1. #1
    Join Date
    Sep 2004
    Posts
    7

    Unhappy 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.

  2. #2
    Join Date
    Aug 2004
    Posts
    12
    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

  3. #3
    Join Date
    Sep 2004
    Posts
    7
    thanks again for your help,

    it came up with an error:

    PHP Code:
    SQLselect 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?

  4. #4
    Join Date
    Aug 2004
    Posts
    12
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •