SQL Puzzle | Club data from two table where data is different puzzle

In this puzzle you have to club data from two table where data is different puzzle. For more details please check the sample input and expected output.

Sample Input

Table1

aId val
1 a
2 b
3 c
4 d

Table2

aId val
1 a
2 x
3 y
4 d

Expected Output

Id val
2 b
3 c
2 x
3 y

Script

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

--

CREATE TABLE testTable1
(
	 aId INT
	,val VARCHAR(1)
)
GO

INSERT INTO testTable1 VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d')
GO

CREATE TABLE testTable2
(
	 aId INT
	,val VARCHAR(1)
)
GO

INSERT INTO testTable2 VALUES
(1,'a'),
(2,'x'),
(3,'y'),
(4,'d')
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 ISNULL(a.aId,b.aId) Id, ISNULL(a.val,b.val) val 
FROM testTable1 a
FULL OUTER JOIN testTable2 b ON a.val = b.val
WHERE a.val IS NULL OR b.val IS NULL

--

Output

--                            

Id          val
----------- ----
2           b
3           c
2           x
3           y

(4 rows 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