how to solve that query in SQL

lina sam · #1 (**permalink**) 12-06-02, 01:46

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

bex · #2 (**permalink**) 12-06-02, 02:46

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)

gle · #3 (**permalink**) 12-06-02, 08:02

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.

Paul Young · #4 (**permalink**) 12-06-02, 08:48

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'

Internet Services

Web Development

Digital Marketing

Consumer Tech