how to join two subqueries?

Question

hi all

i have a select ...

SELECT PAH.AccountDetailRef ,PAI.ProductRef,SUM(PAI.Quanty) AS InQnty 
FROM dbo.ProductActionHeader PAH  
INNER JOIN dbo.ProductActionItem PAI ON PAH.ProductActionHeaderId=PAI.ProductActionHeaderRef  
INNER JOIN dbo.Product P ON P.ProductId =PAI.ProductRef  
WHERE PAH.TypeRef=1   AND P.HasPrice=0  
GROUP BY PAH.AccountDetailRef,PAI.ProductRef

and result is..

AccountDetailRef	ProductRef	InQnty
10001	                      1	         2500
10002	                      1          65
20001	                      1        	50
10002	                      3	         50
20001	                      3  	25500

and another select...

SELECT PAH.AccountDetailRef ,PAI.ProductRef,SUM(PAI.Quanty) AS OutQnty
 FROM dbo.ProductActionHeader PAH  
 INNER JOIN dbo.ProductActionItem PAI ON PAH.ProductActionHeaderId=PAI.ProductActionHeaderRef  
 INNER JOIN dbo.Product P ON P.ProductId =PAI.ProductRef  
 WHERE PAH.TypeRef=2   AND P.HasPrice=0  
 GROUP BY PAH.TypeRef,PAH.AccountDetailRef,PAI.ProductRef  

by this result...

AccountDetailRef	ProductRef	OutQnty
20001	                       1	  80
10002	                       3	25050

now i join two select by this code

;WITH InSum AS (SELECT PAH.AccountDetailRef ,PAI.ProductRef,SUM(PAI.Quanty) AS InQnty 
FROM dbo.ProductActionHeader PAH  
INNER JOIN dbo.ProductActionItem PAI ON PAH.ProductActionHeaderId=PAI.ProductActionHeaderRef  
INNER JOIN dbo.Product P ON P.ProductId =PAI.ProductRef  
WHERE PAH.TypeRef=1   AND P.HasPrice=0  
GROUP BY PAH.AccountDetailRef,PAI.ProductRef  )
,OutSum AS(SELECT PAH.AccountDetailRef ,PAI.ProductRef,SUM(PAI.Quanty) AS OutQnty
 FROM dbo.ProductActionHeader PAH  
 INNER JOIN dbo.ProductActionItem PAI ON PAH.ProductActionHeaderId=PAI.ProductActionHeaderRef  
 INNER JOIN dbo.Product P ON P.ProductId =PAI.ProductRef  
 WHERE PAH.TypeRef=2   AND P.HasPrice=0  
 GROUP BY PAH.TypeRef,PAH.AccountDetailRef,PAI.ProductRef   )
 SELECT I.AccountDetailRef,I.ProductRef
 ,SUM(I.InQnty) AS InQnty,SUM(O.OutQnty) AS OutQnty
  FROM InSum I
  LEFT JOIN OutSum O ON O.AccountDetailRef =I.AccountDetailRef 
GROUP BY I.AccountDetailRef,I.ProductRef

and get this result

how to join two subqueries and get best result?

please help me

thanks

---

Name of Allah, Most Gracious, Most Merciful and He created the human

Answer 1

looks like you are doing the inner join in the two select in the third queries using CTE's...so what is your question?? also, you can simplify it as below..

SELECT PAH.AccountDetailRef ,PAI.ProductRef,SUM( CASE WHEN PAH.TypeRef=1  THEN PAI.Quanty ELSE 0 END) AS InQnty,
 SUM( CASE WHEN PAH.TypeRef=2  THEN PAI.Quanty ELSE 0 END) AS OutQnty
FROM dbo.ProductActionHeader PAH  
INNER JOIN dbo.ProductActionItem PAI ON PAH.ProductActionHeaderId=PAI.ProductActionHeaderRef  
INNER JOIN dbo.Product P ON P.ProductId =PAI.ProductRef  
WHERE  P.HasPrice=0  
GROUP BY PAH.AccountDetailRef,PAI.ProductRef

---

Hope it Helps!!

Answer 2

looks like you are doing the inner join in the two select in the third queries using CTE's...so what is your question?? also, you can simplify it as below..

SELECT PAH.AccountDetailRef ,PAI.ProductRef,SUM( CASE WHEN PAH.TypeRef=1  THEN PAI.Quanty ELSE 0 END) AS InQnty,
 SUM( CASE WHEN PAH.TypeRef=2  THEN PAI.Quanty ELSE 0 END) AS OutQnty
FROM dbo.ProductActionHeader PAH  
INNER JOIN dbo.ProductActionItem PAI ON PAH.ProductActionHeaderId=PAI.ProductActionHeaderRef  
INNER JOIN dbo.Product P ON P.ProductId =PAI.ProductRef  
WHERE  P.HasPrice=0  
GROUP BY PAH.AccountDetailRef,PAI.ProductRef

---

Hope it Helps!!

thanks dear for your help (-:

---

Name of Allah, Most Gracious, Most Merciful and He created the human