Getting a COUNT from one table and joining it to results from another.

[Red_Wizard_Shot_The_Food]

Say I have a table with Trucks on it TRUCK_ID, DriverName e.g: 211, John 112, Sarah 456, Philip And another table showing when a parcel was loaded on and off. Tuck_IDOFF, TRUCK_IDON e.g 211, 112 (Loaded off of John and onto Sarah) 456, 211 (Loaded off of Philip and onto John) Now I want to run a query that will return: Tuck_ID, DriverName, Total number of offloaded (COUNT of where TRUCK_ID = Tuck_IDOFF) total number loaded (COUNT of where TRUCK_ID = Tuck_IDON) so it would return: 211, John, 1, 1 112, Sarah, 0, 1 456, Philip, 1, 0 I know how to return the data form table 1 just fine, and the counts from table two .... It's joining the 2 results into one set of results that it causing me problems. Cheers.

[andyharman]

select T.Truck_ID, T.DriverName,
  (select count(*) from TruckLoad where Truck_IDOFF = T.Truck_ID) OffloadedCount,
  (select count(*) from TruckLoad where Truck_IDON = T.Truck_ID) LoadedCount
from Truck T
order by T.Truck_ID