开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜