SQL Puzzle | The Status Puzzle

Write a T-SQL to fetch all the rows from table11 and all rows from table21 where status from table11 is 1 and table21 status is 0. We also have to display rows from table11 which are not present in table21

Please check out the sample input and the expected output.

Sample Inputs

Table11

Table21

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data

--

CREATE TABLE Table11 
(ItemNo VARCHAR(255),sStatus int)
GO

CREATE TABLE Table21 
(ItemNo VARCHAR(255),sStatus int)
GO

INSERT INTO Table11  VALUES ('Part1',1) 
INSERT INTO Table11  VALUES ('Part2',1) 
INSERT INTO Table11  VALUES ('Part3',1) 
INSERT INTO Table11  VALUES ('Part4',5) 
INSERT INTO Table11  VALUES ('Part5',1) 
GO

INSERT INTO Table21  VALUES ('Part1',0) 
INSERT INTO Table21  VALUES ('Part2',1) 
INSERT INTO Table21  VALUES ('Part3',1) 
INSERT INTO Table21  VALUES ('Part4',0) 
GO

SELECT * FROM Table11 
SELECT * FROM Table21

--

—

SOLUTION – 1

--

SELECT 
		a.*,ISNULL(b.sstatus,0) sstatus
FROM table11 a LEFT JOIN table21 b
ON a.itemno = b.itemno
WHERE a.sstatus = 1 and ISNULL(b.sstatus,0) = 0
--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com