کدام تامین کننده در مجموع قطعات بیشتری را تامین کرده است؟

سوال

سلام
جداول پایگاه داده :

Suppliers(sid,sName,city) , Parts(pId,pName) and dbo.SP(Sid, Pid, QTY
dbo.sp shows for example supplier s1, supplies 1000 of part p1 . QTY is the quantity.

Query: Which supplier(s), on the whole, supplies the least quantity of parts?

My Answer:

select s.sid, s.sName , sum(sp.QTY) summ,min(sp.QTY) min
from Suppliers s left join sp on s.sid= sp.Sid
–where s.sid = 2
group by s.sid, s.sName
having sum(sp.QTY) <= ALL(select sum(sp.QTY) from sp group by sp.sid)

Problem:
if a supplier has not supplied any parts, it does NOT come in the query result, because it has no record in the dbo.sp table.
thanks in advance.

[۱]: https://i.stack.imgur.com/oHUI8.png

0
Expert 4 ماه 0 پاسخ ها 32 دیده شده 0

ارسال یک پاسخ