how to create a subquery in sql using count based on outer query
- by user1754716
I hope someone can help me with this query. Basically I have two queries that I want to "combine". I want the second query as an extra column along with the first query.
The first one is this :
SELECT t_Item_Storage_Location.Storage_Loc_Nbr,
t_Storage_Location.Storage_Loc_Type_Code,
Count(t_Load.Load_Id) AS CurrentLoadCount,
t_load.MMM_Id_Nbr
FROM t_Load INNER JOIN (t_Storage_Location INNER JOIN t_Item_Storage_Location ON
t_Storage_Location.Storage_Loc_Nbr = t_Item_Storage_Location.Storage_Loc_Nbr) ON
(t_Load.Storage_Loc_Nbr = t_Item_Storage_Location.Storage_Loc_Nbr)
AND (t_Load.MMM_Id_Nbr = t_Item_Storage_Location.MMM_Id_Nbr)
where ((((t_Item_Storage_Location.MMM_Id_Nbr) Between '702004%' And '702011%')
AND ((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%A')
AND ((t_Storage_Location.Storage_Loc_Type_Code)='CD')
AND ((t_Load.Active_Status_Ind)='A')
AND ((t_Load.QC_Status_Code) Like 'R%')
AND ((t_Load.MMM_Facility_Code)='MC'))
OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%B'))
OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%C'))
OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%D'))
OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%E'))
)
GROUP BY t_Item_Storage_Location.MMM_Id_Nbr,
t_Item_Storage_Location.Storage_Loc_Nbr,
t_Storage_Location.Storage_Loc_Type_Code,
t_Load.MMM_Facility_Code,
t_load.MMM_Id_Nbr
HAVING
Count(t_Load.Load_Id)<4
The second one, is based on the t_load.MMM_Id_Nbr of the first one. Basically I want a count of all the loads with that mmm_id_nbr.
SELECT count(Load_ID) as LoadCount, MMM_Id_Nbr, storage_Loc_Nbr
FROM t_load
WHERE QC_Status_Code like 'R%' and mmm_Facility_Code ='MC' and Active_Status_Ind='A'
GROUP by MMM_Id_Nbr, storage_loc_Nbr
