views:

545

answers:

6

Is it possible to UNION queries from tables or views that don't have any result in common? What I'm trying to do is combine data from different views into one result. I have

select a,b,null,c from VIEW1, VIEW2 where VIEW1.a = VIEW2.a
UNION
select null,null,z,null from VIEW3

I would like the result to be a,b,z,c. Is this where I would use select from? What does that look like?

select ? from (
select a,b,null,c from VIEW1, VIEW2 where VIEW1.a = VIEW2.a
UNION
select null,null,z,null from VIEW3)

I'm using MS SQL Server and the views do not have primary keys. Thanks so much.

+1  A: 

If I understand this: are you trying to 'collapse' your results and get rid of all the NULLs? If so, will ever result from view3 correspond to a result from view1/view2? If so, what is the relationship? If not, do the number of results at least match?

Joel Coehoorn
I used the NULLs as placeholders w/the understanding that in UNIONs, the result sets must have the same # of columns. I do have View1.z, but when I put it in the where clause of my 1st single query, I lost all rows from View1 where there was no matching row in View3--and I need those View1 rows! I need all my result rows from View1 whether or not there's a match in View3, but when I tried a single query w/an outer join, I outer joined View1 and View2, then outer joined again w/View3. Maybe I should try 1 query with 2 outer joins again?
A: 

Can it be that you are looking for something like this? (just a wild guess)

SELECT 
  VIEW1.a,
  VIEW1.b,
  (SELECT TOP 1 z FROM VIEW3) AS z,
  VIEW2.c
FROM
  VIEW1, VIEW2 
WHERE
  VIEW1.a = VIEW2.a
Tomalak
+1  A: 

If I understand your question, you're probably getting results like this:

a1, b1, null, c1
a2, b2, null, c2
a3, b2, null, c3
null, null, z1, null
null, null, z2, null
null, null, z3, nul

l

.. but what you're trying to get are results like this:

a1, b1, z1, c1
a2, b2, z2, c2
a3, b2, z3, c3

Do I understand the problem correctly?

If this is correct, you'll need to have a way to join these subqueries together, so that you can tell SQL that the 1's go together, and the 2's go together, and so on.

D. Lambert
Yep--you got it! So, that's my question: is there a beginner way to join the queries? I do actually have z in View1, but when I put this in my original single query as where View1.z = View3.z, I lost all the rows in View1 where I didn't get a match--and I need all the rows returned from View1 even if there's no match on z. I tried an outer join on View, View2, where I always get a hit, then OJ again on View3, but that didn't seem to work. When I switched to 2 queries, I used NULLs as placeholders because the # of columns has to be the same in all queries in a union, right?
D. Lambert
Thank you! That worked for me.
+2  A: 

A union concatenates result sets, it does not combine them.

So what you will get from your first query is this:

  a      b    (null)  c
(null) (null)   z    (null)

If you want to combine them, you'll have to join them, and then you need to have something in common, or you'll have to combine the data in the program.

Do you only have 1 row from each?

If so, then if the pattern above is always going to be like that, this would work:

SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
    SELECT 1 k, View1.a, b, NULL z, c
    FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
    SELECT 1 k, NULL a, NULL b, z, NULL c
    FROM View3) SQ2 ON SQ1.k = SQ2.k

However, if you don't know whether View3.a has a value or View1.a has a value, and you want the one from the first query if there is a value 3, then this would work:

SELECT COALESCE(SQ1.a, SQ2.a) a, COALESCE(SQ1.b, SQ2.b) b,
    COALESCE(SQ1.z, SQ2.z) z, COALESCE(SQ1.c, SQ2.c) c
FROM (
    SELECT 1 k, View1.a, b, NULL z, c
    FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
    SELECT 1 k, NULL a, NULL b, z, NULL c
    FROM View3) SQ2 ON SQ1.k = SQ2.k

But, and there's a big BUT in here. If you have more than one row in either of the views, you'll going to end up with data that doesn't belong together. In that case, you must have something in common.

Here's the full code that I tried, along with the results:

USE master
GO

DROP DATABASE TestDB
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE View1
(
    a INT,
    b INT,
    c INT
)
GO

CREATE TABLE View2
(
    a INT,
    z INT
)
GO

CREATE TABLE View3
(
    z INT
)
GO

INSERT INTO View1 (a, b, c) VALUES (10, 20, 30)
GO

INSERT INTO View2 (a, z) VALUES (10, 40)
GO

INSERT INTO View3 (z) VALUES (50)
GO


SELECT View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a
UNION
SELECT NULL a, NULL b, z, NULL c
FROM View3

SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
    SELECT 1 k, View1.a, b, NULL z, c
    FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
    SELECT 1 k, NULL a, NULL b, z, NULL c
    FROM View3) SQ2 ON SQ1.k = SQ2.k

SELECT COALESCE(SQ1.a, SQ2.a) a, COALESCE(SQ1.b, SQ2.b) b,
    COALESCE(SQ1.z, SQ2.z) z, COALESCE(SQ1.c, SQ2.c) c
FROM (
    SELECT 1 k, View1.a, b, NULL z, c
    FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
    SELECT 1 k, NULL a, NULL b, z, NULL c
    FROM View3) SQ2 ON SQ1.k = SQ2.k

Results:

a           b           z           c
----------- ----------- ----------- -----------
NULL        NULL        50          NULL
10          20          NULL        30

(2 row(s) affected)

a           b           z           c
----------- ----------- ----------- -----------
10          20          50          30

(1 row(s) affected)

a           b           z           c
----------- ----------- ----------- -----------
10          20          50          30

(1 row(s) affected)

If you add one single row to View3, like this:

INSERT INTO View3 (z) VALUES (51)

Then you'll get these results, notice the doubled rows:

a           b           z           c
----------- ----------- ----------- -----------
NULL        NULL        50          NULL
NULL        NULL        51          NULL
10          20          NULL        30

(3 row(s) affected)

a           b           z           c
----------- ----------- ----------- -----------
10          20          50          30
10          20          51          30

(2 row(s) affected)

a           b           z           c
----------- ----------- ----------- -----------
10          20          50          30
10          20          51          30

(2 row(s) affected)
Lasse V. Karlsen
I think I did not explain well. I do have 100s of rows in each view. I do have z in View1, and I initially used a single query. But when I equate View1.z to View3.z and there is no row in View3 with that z value, I lose the row from View1. I tried an OUTER JOIN, but my result wasn't correct, so I switched to 2 queries. I don't have anything in common between View1 and View3 if View3 is missing a row with that z value, so if I understood your BUT, then I can't do what I need to do. Is that correct? (Thank you for your effort. I really appreciate that!)
If you don't have anything in common, you can't join them, it won't make sense. You can fake something in common, I'll get back later tonight with an example, but it won't be good.
Lasse V. Karlsen
A: 

The results from any sql select, including a union, are a static set of columns. (No polymorphism.)

But you don't have to use all of the columns in each row, you use null values in the rows where the column doesn't have a value. I also suggest that you include a 'type' row so the client can tell the type (and interesting columns) for a given row.

Example:

 (Select
   'room' as view_type
 , rooms.room as room
 , NULL as color
 From rooms 
 )
UNION ALL
(Select
  'color' as view_type
 , NULL as room
 , colors.color as color
 From colors 
)
Larry K
A: 

You actually want a cross join:

select v1.a,v1.b,VIEW3.z,v1.c from (SELECT a,b,c, FROM VIEW1, VIEW2 where VIEW1.a = VIEW2.a) as v1 CROSS JOIN VIEW3

AlexKuznetsov
If I use a CROSS JOIN, won't I get a cartesian product between v1 and VIEW3? Each of the views has 100s of rows.