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

DJ1

DJ2

Expected output

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• 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 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