How to combine amount scored and amount needed from 2 tables in 1 row
-
I have 3 tables, ID's, Scored and Needed. Here are some testdata and the result I would like to show:
SELECT
a.*, s.Kind, s.Score, n.Kind, n.Need FROM
( -- Test Data base
select ID = 1 , aName = 'ID1' union all
select ID = 2 , aName = 'ID2' union all
select ID = 3 , aName = 'ID3' union all
select ID = 4 , aName = 'ID4' union all
select ID = 5 , aName = 'ID5'
) a
LEFT OUTER JOIN
( -- Test Data amount scored
select ID = 2 , Kind = 1, Score = 1 union all
select ID = 2 , Kind = 2, Score = 1 union all
select ID = 3 , Kind = 1, Score = 1 union all
select ID = 4 , Kind = 1, Score = 1
) s
ON a.ID = s.ID
LEFT OUTER JOIN
( -- Test Data amount needed
select ID = 1 , Kind = 1, Need = 1 union all
select ID = 2 , Kind = 1, Need = 1 union all
select ID = 2 , Kind = 2, Need = 3 union all
select ID = 3 , Kind = 1, Need = 2 union all
select ID = 3 , Kind = 3, Need = 1
) n
ON a.ID = n.ID --and s.Kind = n.Kind
/* -- Result required --ID aName Kind Need Score
-- ----- ---- ---- -----
1 ID1 1 1 NULL
2 ID2 1 1 1
2 ID2 2 3 1
3 ID3 1 2 1
3 ID3 3 1 NULL
4 ID4 1 NULL 1
(5 ID5 1 NULL NULL)
-- row ID5 may left out */
Is this possible?
With a simple "and s.Kind = n.Kind" I miss some rows 🙁
-
-- Use a full outer join
WITH CTE(ID,Kind,Need,Score) AS (SELECT COALESCE(s.ID,n.ID),
COALESCE(s.Kind,n.Kind),
n.Need,s.Score
FROM
( -- Test Data amount scored
select ID = 2 , Kind = 1, Score = 1 union all
select ID = 2 , Kind = 2, Score = 1 union all
select ID = 3 , Kind = 1, Score = 1 union all
select ID = 4 , Kind = 1, Score = 1
) s
FULL OUTER JOIN
( -- Test Data amount needed
select ID = 1 , Kind = 1, Need = 1 union all
select ID = 2 , Kind = 1, Need = 1 union all
select ID = 2 , Kind = 2, Need = 3 union all
select ID = 3 , Kind = 1, Need = 2 union all
select ID = 3 , Kind = 3, Need = 1
) n ON n.ID=s.ID AND n.Kind=s.Kind
)
SELECT
a.*, c.Kind, c.Need, c.Score FROM
( -- Test Data base
select ID = 1 , aName = 'ID1' union all
select ID = 2 , aName = 'ID2' union all
select ID = 3 , aName = 'ID3' union all
select ID = 4 , aName = 'ID4' union all
select ID = 5 , aName = 'ID5'
) a
LEFT OUTER JOIN CTE c ON a.ID = c.ID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, thank you :Wow:
I am impressed.
At this moment I don't understand the way it works.
But I'll try to analyze the parts, to find out what happens inside your code.
The CTE is a kind of a subtable in memory, I think. Filled with all occurring ID's and Kinds. And Scores and Needs.
The full outer join helps to fill it with all combinations, I think.
It is a bit complex for a beginner like me :blink:
-
Mark, I found out the way your code works, by recoding it without the CTE:
SELECT a.aName, c.* FROM
( -- Test Data base
select ID = 1 , aName = 'ID1' union all
select ID = 2 , aName = 'ID2' union all
select ID = 3 , aName = 'ID3' union all
select ID = 4 , aName = 'ID4' union all
select ID = 5 , aName = 'ID5'
) a
LEFT OUTER JOIN
(SELECT COALESCE(s.ID,n.ID) AS ID,
COALESCE(s.Kind,n.Kind) AS Kind,
n.Need, s.Score
FROM
( -- Test Data amount scored
select ID = 2 , Kind = 1, Score = 1 union all
select ID = 2 , Kind = 2, Score = 1 union all
select ID = 3 , Kind = 1, Score = 1 union all
select ID = 4 , Kind = 1, Score = 1
) s
FULL OUTER JOIN
( -- Test Data amount needed
select ID = 1 , Kind = 1, Need = 1 union all
select ID = 2 , Kind = 1, Need = 1 union all
select ID = 2 , Kind = 2, Need = 3 union all
select ID = 3 , Kind = 1, Need = 2 union all
select ID = 3 , Kind = 3, Need = 1
) n
ON n.ID=s.ID AND n.Kind=s.Kind
)c ON a.ID = c.ID
/* -- Result required --
ID aName Kind Need Score
-- ----- ---- ---- -----
1 ID1 1 1 NULL
2 ID2 1 1 1
2 ID2 2 3 1
3 ID3 1 2 1
3 ID3 3 1 NULL
4 ID4 1 NULL 1
5 ID5 NULL NULL NULL
*/
The CTE version is more elegant, but I am used to the "old way" to combine tables :Whistling:
The full outer join is the essential part of the solution, thank you again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply