'Bringing Subquery results to Outer Query

I have a list of part numbers and corresponding BOM components in a query. I have another query that displays order count and usage history for the kit part numbers. I'm relatively new to SQL Studio and having trouble referencing the subqueries effectively. First post. Not sure why the formatting is so weird. ugh

The below query lists that kit items in the database and corresponding components:

select kp.part_num KitPart
       ,kp.stocked
       ,kp.price_cat
       ,kp.sales_cat
       ,kbom.partnum_assm
       ,kbom.partnum_comp
       ,cp.stocked CompStocked
from part kp
     LEFT join part_bom kbom on kp.part_num=kbom.partnum_assm
     LEFT join part cp on kbom.partnum_comp=cp.part_num
where kp.kit='1'
ORDER BY KP.stocked DESC
        ,KP.part_num

This query lists the count of orders and sum of usage

select invitem.part_num KitPart
      ,count(invitem.inv_num) as Count_of_Orders
      ,SUM(invitem.qty_shipped) as Sum_Of_Usage
from invitem
     join part on invitem.part_num=part.part_num
where part.kit='1'
  and   invitem.misc_date1>DATEADD(m,-36,getdate())
group by invitem.part_num

I want to get the results in the first query just for items that exist in the 2nd query and display the Count_of_Orders and Sum_Of_Usage in the first query for the results.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source