Show names of suppliers who delivered largest delivery (nested query)?
I have a table S
containing suppliers (S, SNAME, CITY), a table P
containing products (P, PNAME, COLOUR, WEIGHT, PRICE) and a table SP
containing deliveries (S, P, QTY) where S and P ar开发者_高级运维e foreign keys to table S and P.
What I have been asked to do is
"Show the name of the supplier(s) who have delivered the largest delivery (as a nested query)."
How would I go about doing this?
EDIT: Removed the piece of code I claimed was working fine. It was not.
select s.sname, sum(sp.qty)
from s,
sp,
(select max(qty) val
from (select sum(sp.qty) qty
from s, sp, p
where sp.p = p.p
and sp.s = s.s
group y s.sname)) max_q
where sp.s = s.s
group by s.sname
having sum(sp.qty) = max_q.val
I found the solution. Too obvious I guess. I had trouble figuring out what inner query to use.
SELECT SNAME FROM S, SP WHERE S.S = SP.S AND QTY = (SELECT MAX(QTY) FROM SP)
精彩评论