Possible Duplicate:
Writing an SQL query to SELECT item from the following table

I am trying to list the names of the suppliers (sname) and part number (pnum) of suppliers who had a shipment of at least two different parts.

Here are the two tables I need to work with...

Shipments Table: http://i44.tinypic.com/1zdcc9j.jpg

Suppliers Table: http://i39.tinypic.com/o6w414.png

What I tried:

This is incorrect, I tried something along these lines...

SELECT snum 
FROM (SELECT snum, count(snum) AS nbr FROM Shipments ) 
WHERE nbr > 1;

Thank you

• sql
• ms-access

SELECT suppliers.sname, shipments.pnum
FROM suppliers, shipments
WHERE shipments.snum = suppliers.snum
GROUP BY suppliers.sname
HAVING count(shipments.pnum) >= 2

You need to be using a GROUP BY clause when using an aggregate function such as COUNT. When using Aggregates and GROUP BY the HAVING CLAUSE acts as your where clause.

This will get you all snums that have a count > 1

SELECT snum
FROM
(
    SELECT snum, count(snum) AS nbr
    FROM Shipiments
    GROUP BY snum
    HAVING COUNT(snum) > 1
) AS T

This will join the table back to the suppliers table to get the supplier name

SELECT DISTINCT Suppliers.sname
FROM
(
    SELECT snum, count(snum) AS nbr
    FROM Shipiments
    GROUP BY snum
    HAVING COUNT(snum) > 1
) AS T INNER JOIN Suppliers ON Suppliers.snum = T.snum

I don't know about Access (as this question is tagged as such), but this should work in MySQL:

select su.sname from suppliers su
join shipments sh on su.snum = sh.snum
group by su.sname
having count(distinct su.pnum) >= 2