Tags

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


SQL Puzzle | Find Non matching records from Second table

In this puzzle you need to find the non matching records from column Vals. Need Vals column from SecondTable and Id column from firstTable. For more details please check the sample input and expected output.

Sample Input

First Table

ID Vals
1 a
2 b
3 c
4 NULL

Second Table

ID Vals
1 a
2 NULL
3 z
4 NULL

Expected Output

Id Vals
2 NULL
3 z
4 NULL

Script

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

--

CREATE TABLE firstTable
(
	 ID  INT    	                
	,Vals VARCHAR(10)     
)
GO

INSERT INTO firstTable VALUES
(1    ,'a'),
(2    ,'b'),
(3    ,'c'),
(4	  , NULL)
GO

CREATE TABLE SecondTable
(
	 ID  INT    	                
	,Vals VARCHAR(10)   
)
GO

INSERT INTO SecondTable VALUES
(1    ,'a'),
(2    ,NULL),
(3    ,'z'),
(4    ,NULL) 
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 f.Id,s.Vals FROM firstTable f
INNER JOIN SecondTable s ON f.ID = s.ID 
AND ( f.Vals <> s.Vals OR f.Vals IS NULL OR s.Vals IS NULL )

--

Output-1

--                               
  
Id          Vals
----------- ----------
2           NULL
3           z
4           NULL

(3 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

Advertisements