SQL Server 2008 join and count

Question

I am relatively new to SQL and I am having a really hard time getting this query figured out. I need to show which shipments (shipment_no) were delivered by multiple trucks drivers.

Here are the only two columns in the table (named Package) that I believe I need as well as the entire other table (truck) I am joining it with. As you can see, shipment_no 1775 is the only one that has been delivered by more than one truck/driver.

Package table = Shipment_No - 1770,1771,1772,1773,1774,1774,1774,1775,1775,1775,1776,1777 and Truck_no = 100,103,105,102,108,108,108,101,109,109,100,100 (Respectively)

Truck table = Truck_NO 100,101,102,103,104,105,106,107,108,109 and drivername = JONES,DAVIS,GOMEZ,THOMPSON,HERSHEY,FERRIS,SHAVER,LEE,TOPI,ACKERMAN (Respectively)

This is what I've got so far

select shipment_no, drivername
from package, truck
where package.truck_no=truck.truck_no
group by shipment_no, drivername

My results look like this

 - Shipment_no = 
1770
1771
1772
1773
1774
1775
1775
1776
1777
 - Drivername =
JONES
THOMPSON
FERRIS
GOMEZ
TOPI
ACKERMAN
DAVIS
JONES
JONES

All I need to display is the shipping number in the end so it would look like this.

-Shipment_no

-1775   

I've been trying for hours and any help is appreciated.

Thanks a lot!

Answer 1

Try this:

SELECT Shipment_no
FROM package
GROUP BY Shipment_no
HAVING COUNT(DISTINCT Truck_no) > 1

Answer 2

Select  shipment_no
From    Package
Group BY shipment_no
Having Count(Distinct Truck_No) > 1