Using UNION in your queries
Are you using Union queries yet? If not, you should get up to speed. A union query is an extremely useful method for returning records from different tables in the same recordset. You just have to remember that datatypes of the columns must match in the same order they are referenced. Here's an example:
SELECT ord_no, product,
cost, shipping, status,
OrdDt
FROM Orders
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
UNION
SELECT ord_no, product,
cost, shipping, status,
OrdDt
FROM Orders_history
WHERE OrdDt dateBetween '2005-07-18' AND '2005-07-19'
</cfquery>
In this example we have a system that holds unposted orders in the 'orders' table. When they are posted they go from orders to the orders_history table. The union query above gives me information about the orders placed between specific dates - whether they were posted or not. There are a couple of gotchas. The order of the columns must be of the same data type. That means the columns can be named differently. They are only required to match for type. If the "shipping" column is "shipCost" in the Orders_history table we could easily do the following.
UNION
SELECT ord_no, shipCost
Ordering in a union query
A union query has to have it's "order by" clause exist in the last query specified. It may not be in any other query in the group of queries. Sometimes you an do some clever things with Union queries ordering to give you "groups" of records. For example, let's say, in the query above, that I wanted to group by whether or not the record had been posted or not. In other words, I wanted to order by whether or not the record came from the "orders" table or the "orders_history" table. Obviously, the "status" field would probably provide some help there, but let's say there were multiple possibilities and ordering by "status" would give me more than the 2 groups I desire. Here's what I can do.
SELECT ord_no, product,
cost, shipping, status,
OrdDt, 1 AS sortOrder
FROM Orders
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
UNION
SELECT ord_no, product,
cost, shipping, status,
OrdDt, 2 AS sortOrder
FROM Orders_history
WHERE OrdDt dateBetween '2005-07-18' AND '2005-07-19'
ORDER BY sortOrder
</cfquery>
Simple right? Add an int with the sort order you want and alias it as a column, then sort by that field.
Aggregate Functions using union
Recently a question came up on CF Talk about using UNION to aggregate data from more than 1 table. In other words, can I use my query to return a simple count of all the records from both tables. You do a select COUNT(*) from a subquery to make this happen.
SELECT count(*) FROM
(
SELECT ord_no
FROM Orders
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
UNION ALL
SELECT ord_no
FROM Orders_history
WHERE OrdDt dateBetween '2005-07-18' AND '2005-07-19'
) AS totalOrders
</CFQUERY>
2 Comments
-
I was stuck with similar problem of sorting on union queries and it works. Thanks a million.
Regards -
Regarding heading 'Ordering in a union query'. With this solution UNION will have duplicate records. Because for UNION sort_order is different in Query1 and Query2.
That mean after UNION you can have results like:
col1 | col2 | sort_order |
abc | 123 | 1 | // From Query1
abc | 123 | 2 | // From Query2
Now both the rows are logically same. But these are different because of sort_order thing.
I've spent a lot of time to find a solution for this. Please let me know if you have any solution in mind. Thanks
Adil
Sites to Watch
Aggregators
RSS
