T-SQL Query | [ The Join and the Aggregate Puzzle ]

The puzzle is very simple. We have 3 tables named DJ, DJ1, DJ2. We have get aggregated values of Val from table DJ1 and DJ2 . Please check out the sample input and expected output for details.

Sample Input

DJ

 ID 1 2 3 4

DJ1

 ID Val 1 100 2 200 3 500 3 500 2 100

DJ2

 ID Val 2 10000 3 500 2 100 4 100 4 900

Expected output

 ID Val1 Val2 1 100 0 2 300 10100 3 1000 500 4 0 1000

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

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

 ``` -- CREATE TABLE DJ ( ID INT ) GO INSERT INTO DJ(ID) Values (1),(2),(3),(4) GO CREATE TABLE DJ1 ( ID INT ,Val INT ) GO INSERT INTO DJ1(ID,Val) VALUES (1,100),(2,200),(3,500),(3,500),(2,100) GO CREATE TABLE DJ2 ( ID INT ,Val INT ) GO INSERT INTO DJ2(ID,Val) VALUES (2,10000),(3,500),(2,100),(4,100),(4,900) GO -- ```

UPDATE – 10-Apr-2015 – Solution 1

 ``` -- Solution 1 -- SELECT d.ID , ISNULL((SELECT SUM(d1.Val) FROM DJ1 d1 WHERE d1.ID = d.ID ),0) Val1 , ISNULL((SELECT SUM(d2.Val) FROM DJ2 d2 WHERE d2.ID = d.ID ),0) Val2 FROM DJ d -- ```

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

Keep Learning

http://MSBISkills.com