'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 |
|---|
