Can someone assist me with an SQL query?

Question

I have the following tables:
Parts: pnumber, pname, color, weight, city
Shipments: snumber, pnumber, quantity, shipment_ID
Suppliers: snumber, sname, status, city

I need to write the following query in SQL. "List the names of those suppliers along with the number of different parts that they ship for those suppliers who have a shimpment of at least two different parts."

I know I have to use the count function but can't figure out the rest.

Answer 1

Not a particularly easy SQL question. I believe this is the answer



SELECT sname, COUNT(DISTINCT pnumber)

FROM parts pa, shipments sh, supplier su

WHERE su.snumber = sh.snumber

AND sh.pnumber = pa.pnumber

AND EXISTS

(

SELECT 1

FROM shipments sh2

WHERE sh2.snumber = su.snumber

GROUP BY snumber, shipment_ID

HAVING COUNT(DISTINCT pnumber) >= 2

)

Answer 2

Ugh, a little rusty on my sql, and it would help if i can actually test the query, but if you can try something like this:





select su.sname, count(*) from shipments sh, suppliers su, parts pa

where sh.snumber = su.snumber

and sh.pnumber = pa.pnumber

and count(*) > 2

group by pa.pnumber



Maybe someone can improve my answer.



Good luck!

Answer 3

Believe it or not, one of the easiest and quickest way to develop the basic SQL that you need (and can then tweak for max performance) is using, of all things, Microsoft Access (even an old version 2003)



You set up your tables and relationships (very easy to do in that environment) and then use the graphical query gadget they have where you add the tables and put the things you want to see with conditions etc.

Once you get the answer you need you can view it in SQL.



I use it so that I can get the SQL I need (that works) when I'm writing Visual Basic code or for my PHP code when using MySQL.

That tool/methodology has saved me countless hours and frustration.

Why else do I like it. Not only does it help with the syntax, but it saves on typing.

Answer 4

hint:

having count(shipment_ID)>=2