Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Question Unanswered: how to solve that query in SQL

    I have 6 tables
    1)sale tablehas the following col ( saleno,saleqty,itemname,deptname)
    2) supply table ( splno,splname)
    3) item table( itemname, itemtype, itemcolor)
    4) dept table( deptname, deptfloor,deptphone,empno)
    5) delivery table ( delno, delqty, itemname,deptname,splno)
    6)employee table ( empno, empname, empsalary,deptname bossno)

    the query i want to solve is:
    list the supplier that delivers all and only brown items?
    please if anyone know how, let me know. thanks

  2. #2
    Join Date
    Nov 2002
    Location
    South Africa
    Posts
    9

    Wink Not a lot of info to go on, but here goes

    select * from supply where splno in (select splno from delivery where itemname in
    (select itemname from item where itemcolor= 'BROWN'))

    This will only bring back records for items that have been ordered.

    From what i can see regarding your tables - this is the only link.

    Enjoy,
    Neil
    IT Dept
    Business Express - JNB
    South Africa

    1)sale tablehas the following col ( saleno,saleqty,itemname,deptname)
    2) supply table ( splno,splname)
    3) item table( itemname, itemtype, itemcolor)
    4) dept table( deptname, deptfloor,deptphone,empno)
    5) delivery table ( delno, delqty, itemname,deptname,splno)
    6)employee table ( empno, empname, empsalary,deptname bossno)

  3. #3
    Join Date
    Dec 2002
    Posts
    1

    Re: how to solve that query in SQL

    It the SQL offered is what is required I would personally do the query as:

    SELECT
    S.*
    FROM
    SUPPLY S, DELIVERY D, ITEM I
    WHERE
    S.SPLNO = D.SPLNO AND
    D.ITEMNAME = I.ITEMNAME AND
    I.TEMCOLOR = 'BROWN'

    This is more efficient, I think. and besides the nested sub-querys are not east to read/maintain - and are just not necessary.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you really want to press the issue the following would be the way to go..

    Code:
    select S.* 
      from SUPPLY   S
      inner join DELIVERY D on S.SPLNO = D.SPLNO
      inner join ITEM     I on D.ITEMNAME = I.ITEMNAME
     where I.TEMCOLOR = 'BROWN'
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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