SQL Puzzle | The Single Select – UNION ALL Data Puzzle

In this puzzle we have to combine data from two tables with a condition that you cannot use a UNION ALL and only SELECT select is allowed to get the data.Please check the sample input and expected output.

Sample Input

TABLE A

Id a b
10 100 0
20 100 0
30 100 0

TABLE B

Id a b
40 0 100
20 0 100
50 0 100

Expected Output

Id a b
10 100 0
20 100 100
30 100 0
40 0 100
50 0 100

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE [TABLEA:] ( Id INT ,a INT ,b INT ) GO INSERT INTO [TABLEA:] VALUES (10 ,100 , 0 ), (20 ,100 , 0 ), (30 ,100 , 0 ) GO CREATE TABLE [TABLEB:] ( Id INT ,a INT ,b INT ) GO INSERT INTO [TABLEB:] VALUES (40 , 0 , 100 ), (20 , 0 , 100 ), (50 , 0 , 100 ) GO SELECT * FROM [TABLEA:] GO SELECT * FROM [TABLEB:] GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- SELECT COALESCE(a.id,b.id) Id , ISNULL(a.a,0) + ISNULL(b.a,0) a , ISNULL(a.b,0) + ISNULL(b.b,0) b from [TABLEB:] b FULL OUTER JOIN [TABLEA:] a on a.Id = b.Id ORDER BY COALESCE(a.id,b.id) -- ```

Output

 ```-- /*------------------------ SELECT COALESCE(a.id,b.id) Id , ISNULL(a.a,0) + ISNULL(b.a,0) a , ISNULL(a.b,0) + ISNULL(b.b,0) b from [TABLEB:] b FULL OUTER JOIN [TABLEA:] a on a.Id = b.Id ORDER BY COALESCE(a.id,b.id) ------------------------*/ Id a b ----------- ----------- ----------- 10 100 0 20 100 100 30 100 0 40 0 100 50 0 100 (5 row(s) affected) -- ```

Solution – 2

 ```-- SELECT ISNULL(a.ID,b.iD) id, COALESCE(a.a+ b.a , a.a ,b.a) A , COALESCE(a.b +b.b,a.b,b.b) B from [TABLEA:] a FULL OUTER JOIN [TABLEB:] b on a.id= b.id -- ```

Output

 ```-- /*------------------------ SELECT ISNULL(a.ID,b.iD) id, COALESCE(a.a+ b.a , a.a ,b.a) A , COALESCE(a.b +b.b,a.b,b.b) B from [TABLEA:] a FULL OUTER JOIN [TABLEB:] b on a.id= b.id ------------------------*/ id A B ----------- ----------- ----------- 10 100 0 20 100 100 30 100 0 40 0 100 50 0 100 (5 row(s) affected) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com