CODEWITHSUNDEEP
sqlsql-server
Vince
Vince

Reputation: 47

Getting the difference from 2 select statements which return multiple rows

i have this 2 select statements

SELECT SizeName,SUM(DESIGNSIZERELEASE.Quantity) as [Release]
FROM DESIGNRELEASE JOIN
     DESIGNSIZERELEASE
     ON DESIGNRELEASE.DesignReleaseID = DESIGNSIZERELEASE.DesignReleaseID JOIN
     SIZESET
     ON SIZESET.SizeSetID = DESIGNSIZERELEASE.SizeSetID AND
        DESIGNRELEASE.RevisionNo = SIZESET.RevisionNo
WHERE DESIGNRELEASE.RevisionNo = 2 AND DESIGNRELEASE.DesignID='SD00000'
GROUP BY DESIGNRELEASE.DesignID,Towhom,SizeName
ORDER BY case SizeName when 'XS' then 1 when 'S' then 2 when 'M' then 3 when 'L' then 4 when 'XL' then 5 end

AND 

SELECT SUM(DESIGNRECEIVEDETAILS.Quantity) as [Receive]
FROM DESIGNRELEASE JOIN
     DESIGNSIZERELEASE
     ON DESIGNRELEASE.DesignReleaseID = DESIGNSIZERELEASE.DesignReleaseID JOIN
     SIZESET
     ON SIZESET.SizeSetID = DESIGNSIZERELEASE.SizeSetID AND
        DESIGNRELEASE.RevisionNo = SIZESET.RevisionNo JOIN
     DESIGNRECEIVE
     ON DESIGNRECEIVE.DesignReleaseID = DESIGNRELEASE.DesignReleaseID JOIN
     DESIGNRECEIVEDETAILS
     ON DESIGNRECEIVE.ReceiptID = DESIGNRECEIVEDETAILS.ReceiptID AND
        DESIGNRECEIVEDETAILS.SizeSetID = SIZESET.SizeSetID
WHERE DESIGNRELEASE.RevisionNo = 2 AND DESIGNRELEASE.DesignID='SD00000'
GROUP BY DESIGNRELEASE.DesignID,Towhom,SizeName
ORDER BY case SizeName when 'XS' then 1 when 'S' then 2 when 'M' then 3 when 'L' then 4 when 'XL' then 5 end

having these 2 result

+----------+-------------+    
|Size NAME |RELEASE      |
+----------+-------------+
|S         |20           |
+----------+-------------+
|M         |20           |
+----------+-------------+   

+----------+
|Receive   |
+----------+
|10        |
+----------+
|10        |
+----------+

I need to get the difference of Release and Receive how can i do that ? thanks btw i separated them because i'm not getting the right result when i put them in a single select statement

Upvotes: 0

Views: 58

Answers (1)

user3192244
user3192244

Reputation: 86

I am not sure if you can do this way in mysql but this is something you could do in Oracle...

SELECT TABLE1.SizeName, TABLE1.Release, TABLE2.Receive, (TABLE1.Release - TABLE2.Receive) Difference
    FROM
    (SELECT SizeName, SUM(DESIGNSIZERELEASE.Quantity) as [Release] 
    FROM DESIGNRELEASE JOIN DESIGNSIZERELEASE ON DESIGNRELEASE.DesignReleaseID = DESIGNSIZERELEASE.DesignReleaseID 
    JOIN SIZESET ON SIZESET.SizeSetID = DESIGNSIZERELEASE.SizeSetID AND DESIGNRELEASE.RevisionNo = SIZESET.RevisionNo 
    WHERE DESIGNRELEASE.RevisionNo = 2 AND DESIGNRELEASE.DesignID='SD00000' 
    GROUP BY DESIGNRELEASE.DesignID,Towhom,SizeName 
    ORDER BY case SizeName when 'XS' then 1 when 'S' then 2 when 'M' then 3 when 'L' then 4 when 'XL' then 5 end) TABLE1
    ,
    (SELECT SizeName, SUM(DESIGNRECEIVEDETAILS.Quantity) as [Receive] 
    FROM DESIGNRELEASE JOIN DESIGNSIZERELEASE ON DESIGNRELEASE.DesignReleaseID = DESIGNSIZERELEASE.DesignReleaseID 
    JOIN SIZESET ON SIZESET.SizeSetID = DESIGNSIZERELEASE.SizeSetID AND DESIGNRELEASE.RevisionNo = SIZESET.RevisionNo 
    JOIN DESIGNRECEIVE ON DESIGNRECEIVE.DesignReleaseID = DESIGNRELEASE.DesignReleaseID 
    JOIN DESIGNRECEIVEDETAILS ON DESIGNRECEIVE.ReceiptID = DESIGNRECEIVEDETAILS.ReceiptID 
    AND DESIGNRECEIVEDETAILS.SizeSetID = SIZESET.SizeSetID WHERE DESIGNRELEASE.RevisionNo = 2 
    AND DESIGNRELEASE.DesignID='SD00000' GROUP BY DESIGNRELEASE.DesignID,Towhom,SizeName 
    ORDER BY case SizeName when 'XS' then 1 when 'S' then 2 when 'M' then 3 when 'L' then 4 when 'XL' then 5 end) TABLE2
    WHERE TABLE1.SizeName = Table2.SizeName

Upvotes: 1

Related Questions