Tags

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


T-SQL Query | [ The NON ZERO Puzzle ]

Puzzle Statement

1. We have two table named Atable and Btable
2. In table A we have a column called Value. Now this column contains some zeros.
3. What we have to do is join these two tables and for each given Id return Value from Atable if the Value is Non Zero otherwise return Value from BTable(Value)
4. For details please check out the sample input and expected output
5. Note that – We CANNOT use CASE,IIF,IF,REPLACE,UNION,UNION ALL.

NonZeroPuzzle

Sample Input

ATABLE

Id Value
1 0
2 12
3 9
4 0
5 0

BTABLE

Id Value
1 15
2 5
3 1
4 7
5 10

Expected Output

Id Value
1 15
2 12
3 9
4 7
5 10

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”
  • Your solution should be generic in nature.
  • We CANNOT use CASE,IIF,IF,REPLACE,UNION,UNION ALL.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE Atable
(
	  Id INT
	, Value INT
)
GO

CREATE TABLE Btable 
(
	  Id INT
	, Value INT
)
GO

INSERT INTO Atable VALUES (1,0),(2,12),(3,9),(4,0),(5,0)
INSERT INTO Btable VALUES (1,15),(2,5),(3,1),(4,7),(5,10)


--

Update Apr 29 | Solution 1 & 2 


--

------------------ SOLUTION 1 ----------------------


;WITH CTE AS 
(
    SELECT a.Id , a.Value FROM Atable a WHERE a.Value != 0
)
SELECT b.Id , ISNULL(c.Value,b.Value) Value FROM Btable b
LEFT OUTER JOIN CTE c ON c.Id =b.ID 



------------------ SOLUTION 2 ----------------------


SELECT a.Id, ISNULL(NULLIF(a.Value,0),b.Value) Value
FROM 
Atable a INNER JOIN Btable b ON a.Id = b .Id

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com