SQL connecting One Query to another

Just by looking at it seems like this is just an SQL join (or in Coldfusion just a QoQ) but I ran into a complication because one query contains SQL arithmatic and other doesn’t. Obviously SQL JOIN is also out of the picture.

So this is my solution,

<cfquery name="q1" datasource="myDataSource">
 SELECT T1.ItemNo, T1.UnitSize, T2.uCount, (T1.UnitSize * T2.uCount) AS ItemUsedAmt
 FROM TABLE1 AS T1,
    (SELECT DISTINCT ItemNo, SUM(UnitCount) AS uCount
    FROM TABLE2
    WHERE PO = '#ARGUMENTS.PO#'
    GROUP BY ItemNo) AS T2
 WHERE T2.ItemNo = T1.ItemNo 
 </cfquery>

(my answer is inspired with internet research and I wish i wrote down the website that lead me to this solution.)

Advertisements
SQL connecting One Query to another

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s