SQL PUZZLE | The Except for Multiple Records? | SQL Interview Question

In this puzzle you have to give us all the data from aTable1 except bTable1. The catch here is that if you have single record for Id and Vals in aTable1 and bTable1 then we should include that record in the Output. In the example given below 1,Pawan is a single record and it should be in the output. For more details please see the sample input and expected output.

Sample Input

Table1

Table2

Expected Output

Script – DDL and INSERT Sample Data

--

CREATE TABLE aTable1 
(
     Id INT
	,Vals VARCHAR(20)
)
GO

CREATE TABLE bTable1
(
     Id INT
	,Vals VARCHAR(20)
)
GO

INSERT INTO aTable1 VALUES  
 ('1','Pawan')
,('1','Avtaar')
,('2','Avtaar')

INSERT INTO bTable1 values 
('1','Pawan'),
('1','Avtaar')
GO


SELECT * FROM aTable1
GO

SELECT * FROM bTable1
GO


--

SOLUTION – 1

--

;WITH CTE AS
(
	SELECT c.Id,c.Vals, b.Id bId, b.Vals bVals
	,ROW_NUMBER() OVER (PARTITION BY c.Vals ORDER BY b.id,b.Vals DESC) rnk
	FROM aTable1 c
	FULL JOIN bTable1 b ON c.Id = b.Id AND c.Vals = b.Vals
)
SELECT c.Id,c.Vals FROM CTE c
WHERE bVals IS NULL OR rnk = 1
ORDER BY c.Id

--

Output – 1

--

Id          Vals
----------- --------------------
1           Pawan
2           Avtaar

(2 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com