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. 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.

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