UNION Selects Results in SQL Server
Is it possible to query UNION queries from tables or views that have no general result? What I am 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 will choose? What does it 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 am using MS SQL Server and the views do not have primary keys. Many thanks.
If I understand your question, you are 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 you are trying to get results like this:
a1, b1, z1, c1 a2, b2, z2, c2 a3, b2, z3, c3
Am I understanding the problem correctly?
If that's correct, you will need to concatenate these subqueries together so that you can tell SQL that 1 goes together and 2 goes together, etc.
a source to share
Union joins result sets, it does not join them.
So what you get from the first request:
a b (null) c
(null) (null) z (null)
If you want to combine them, you have to join them, and then you need something in common, or you have to combine data in a program.
Do you only have one line from each?
If this is the case, then if the template above is always like this, this will 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 if View3.a matters or View1.a matters and you want one of the first query, if there is a value of 3, will 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 is a big BUT, here. If one of the views has multiple rows, you will get data that does not belong to each other. In this case, you must have something in common.
Here is the complete code I've tried and also 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 line to View3, for example:
INSERT INTO View3 (z) VALUES (51)
Then you get these results, notice the doubled lines:
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)
a source to share
For multiple entries, the ROW_NUMBER () approach worked for me. The Select 1 k approach returned a Cartesian product.
SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a) k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY b) k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
a source to share
The results of any sql selection including joins are a static set of columns. (No polymorphism.)
But you don't need to use all columns in every row, you use null values in rows where the column doesn't matter. I also suggest including a type line so that the client can specify the type (and interesting columns) for the given line.
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
)
a source to share