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

Something you shouldn’t be doing in SQL View tables

Hands down everyone agrees SQL view tables are very nifty and make life so much easier (for programmers, that is).

Today SQL Team’s Jeff¬†posted a very educational article about when it comes to use SQL view table. It’s something I can’t believe that happens, so Thank you. I bet this is going to be useful for many more.

BTW, I highly recommend subscribing to SQL Team because they always have good stuff to share.  

Something you shouldn’t be doing in SQL View tables