How to create complex Queries.

[qwert231]

You ever need to get a combination of data, and can't quite figure out how to go about it? Well, being a newbie, I found this to be true. But I created a query I am quite proud of. Some of you may want to chew it up and spit it out, but for what I need to do, it sure beats some of the alternatives. I wanted to build a query that would first group things together and then sum the quantities. Here is what the query looks like in SQL.

SELECT Package, Sum(Quantity) AS TotQuantity FROM (select P1 as Package, Q1 as Quantity from tblProofOrders where P1 is not null and Q1 is not null AND OrderID LIKE '912227' UNION ALL select P2 as Package, Q2 as Quantity from tblProofOrders where P2 is not null and Q2 is not null AND OrderID LIKE '912227' UNION ALL select P3 as Package, Q3 as Quantity from tblProofOrders where P3 is not null and Q3 is not null AND OrderID LIKE '912227' UNION ALL select P4 as Package, Q4 as Quantity from tblProofOrders where P4 is not null and Q4 is not null AND OrderID LIKE '912227' UNION ALL select P5 as Package, Q5 as Quantity from tblProofOrders where P5 is not null and Q5 is not null AND OrderID LIKE '912227') GROUP BY Package;

And then I turned it into an ASP call to a function I built for recordsets.

tempText = ""
tempText = tempText & "SELECT Package, Sum(Quantity) AS TotQuantity"
tempText = tempText & " FROM (select P1 as Package, Q1 as Quantity from tblProofOrders where P1 is not null and Q1 is not null AND OrderID LIKE '" & orderID & "'"
tempText = tempText & " UNION ALL select P2 as Package, Q2 as Quantity from tblProofOrders where P2 is not null and Q2 is not null AND OrderID LIKE '" & orderID & "'"
tempText = tempText & " UNION ALL select P3 as Package, Q3 as Quantity from tblProofOrders where P3 is not null and Q3 is not null AND OrderID LIKE '" & orderID & "'"
tempText = tempText & " UNION ALL select P4 as Package, Q4 as Quantity from tblProofOrders where P4 is not null and Q4 is not null AND OrderID LIKE '" & orderID & "'"
tempText = tempText & " UNION ALL select P5 as Package, Q5 as Quantity from tblProofOrders where P5 is not null and Q5 is not null AND OrderID LIKE '" & orderID & "')"
tempText = tempText & " GROUP BY Package;"

Response.Write tempText & &quot;<br>&quot;
RecSelectW tempText

Hope this helps somebody. Got questions? mark@centurycolor.com