Last post Jun 20, 2008 07:16 PM by sliprix
Jun 18, 2008 02:31 PM|sliprix|LINK
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run
into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or
if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
Jun 18, 2008 02:41 PM|Gaucho|LINK
Use the UNION operator, as below:
SELECT DataName, DataNotes AS Whatever FROM
WHERE DataName = 'Blue'
SELECT DataName, DataLocation AS Whatever FROM
WHERE DataName = 'Red'
...when you use UNION, each WHERE statement must return an equal number of columns. As in the above example, where each "unioned" WHERE returns two columns of data. The return set will appear as one table, so you may need to name your columns accordingly...
Jun 18, 2008 02:41 PM|ramblor|LINK
If you want to combine two queries into a single resultset you can use
UNION. There's more info on it here -
Jun 18, 2008 02:48 PM|sliprix|LINK
...when you use UNION, each WHERE statement must return an equal number of columns
This poses a problem, I have three columns being returned by one query and two by the other. Is there a way to do this? Also I am wondering if there is a way to sum a column from the frist query with a column of the second query in the union? Also if
possible I would like to avoid doing five subqueries, it seems very inefficient.
Jun 18, 2008 02:58 PM|lberan|LINK
You could add an empty column to that query with two columns, such as:
select column1, column2, column3 from table1 where column1 = 'value'
select column1, column2, '' from table1 where column1 = 'value2'
Jun 18, 2008 02:59 PM|Gaucho|LINK
When I've run into this, I do something like add a third column to my additional WHERE statement to allow the UNION operator to do it's job. It's not exactly elegant, but it works. As far as using the SUM aggregator to total up two columns as you refer
to, you start getting into GROUP BY clauses which are going to conflict with your UNION operator. You might want to explore combining these statements using some type of subquery.
Jun 18, 2008 03:24 PM|smetzger|LINK
You could use a user defined function on the server. User defined functions can return a 'table' and can be used in an sql statement just like a table. This should allow you to do a sum in your function and then in your calling sql use a union.
You could use two separate Gridviews one directly below the other one and without a header.
Jun 18, 2008 03:27 PM|limno|LINK
You can look into another option with CASE to solve your problem. You can post some sample data and your table structure along with your expexted result. Thanks.
Jun 18, 2008 09:14 PM|LockH|LINK
As previously advised, add a dummy third column to one of the queries.
Create a view containing the UNIONed queries.
Query the view, which will look like a single table, and you can sum and group as required.
Jun 20, 2008 07:16 PM|sliprix|LINK
I actually ended up using the LEFT OUTER JOINs with a few subqueries as well. Everything worked out in the end. Thanks for the help!