November 5, 2009 at 3:16 pm
Here's the situation simplified:
I have two tables: a reference table and a mapping table. The mapping table is populated with the PK from the reference table plus another ID
Reference Table
KeyId, Code
1, A
2, B
3, C
Mapping Table
KeyId, MappingId
1, 10
2, 10
1, 25
3, 25
The query I'm trying to build has to return mapped Key Ids and any missing Key Ids for EACH mapping. A simple LEFT JOIN/IS NULL query will not work since all KeyIds are in the mapping table (for the example), but not necessarily used in every mapping.
Example Final Resultset:
MappingId, KeyId, IsMapped
10, 1, Yes
10, 2, Yes
10, 3, No
25, 1, Yes
25, 2, No
25, 3, Yes
I've been trying UNIONS, CTEs and such, but I'm still baffled. This should not be that uncommon. Any help in kick starting this would be extremely helpful.
Thanks,
Steve
Steve Kumbsky
November 5, 2009 at 3:26 pm
Hi Steve
You can use a CROSS JOIN to get all required combinations. After that use a LEFT JOIN to find the missing rows:
DECLARE @r TABLE (Id INT, Code CHAR(1));
DECLARE @m TABLE (KeyId INT, Mapping INT);
INSERT INTO @r
SELECT '1', 'A'
UNION ALL SELECT '2', 'B'
UNION ALL SELECT '3', 'C'
INSERT INTO @m
SELECT '1', '10'
UNION ALL SELECT '2', '10'
UNION ALL SELECT '1', '25'
UNION ALL SELECT '3', '25'
; WITH
mappings (mapping) AS
(
SELECT DISTINCT
Mapping
FROM @m
),
required (KeyId, Mapping) AS
(
SELECT
r.Id
,m.mapping
FROM mappings m
CROSS JOIN @r r
)
SELECT
r.*
,CASE WHEN m.KeyId IS NULL THEN 'No' ELSE 'Yes' END
FROM required r
LEFT JOIN @m m ON r.KeyId = m.KeyId AND r.Mapping = m.Mapping
Greets
Flo
November 6, 2009 at 9:31 am
Florian,
Thanks for the reply and code sample! This is exactly the help i needed. Hope I can do the same for someone in the future.
Steve Kumbsky
November 6, 2009 at 9:38 am
Glad I could help and thanks for the feedback! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply