SQL Server question - subqueries in column result with a join?

Question

I have a distinct list of part numbers from one table. It is basically a table that contains a record of all the company's part numbers. I want to add columns that will pull data from different tables but only pertaining to the part number on that row of the distinct part list.

For example: if I have part A, B, C from the unique part list I want to add columns for Purchase quantity, repair quantity, loan quantity, etc... from three totally unique tables.

So it's almost like I need 3 subqueries that will sum of that data from the different tables for each part.

Can anybody steer me in the direction of how to do this? Please and thank you so much!

Answer 1

One method is correlated subqueries. Something like this:

select p.*,
       (select count(*)
        from purchases pu
        where pu.part_id = p.part_id
       ) as num_purchases,
       (select count(*)
        from repairs r
        where r.part_id = p.part_id
       ) as num_repairs,
       (select count(*)
        from loans l
        where l.part_id = p.part_id
       ) as num_loans
from parts p;

Another option is joins with aggregation before the join. Or lateral joins (which are quite similar to correlated subqueries).