Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

Advertisements