Need help in building query
-
Schema Definitions
CREATE TABLE #tmpMappings
(
MappingTypeID TINYINT NOT NULL,
MappingID TINYINT NOT NULL,
ReferenceID TINYINT NOT NULL
)
CREATE TABLE #tmpSelectedMappings
(
SelectedTypeID TINYINT NOT NULL,
SelectedID TINYINT NOT NULL
)
INSERT INTO #tmpMappings( MappingTypeID, MappingID, ReferenceID )
SELECT 10, 1, 1
UNION ALL
SELECT 11, 4, 1
UNION ALL
SELECT 12, 7, 1
UNION ALL
SELECT 10, 2, 3
UNION ALL
SELECT 11, 18, 3
UNION ALL
SELECT 12, 20, 3
-- EXAMPLE 1
INSERT INTO #tmpSelectedMappings( SelectedTypeID, SelectedID )
SELECT 10, 1
UNION ALL
SELECT 11, 4
UNION ALL
SELECT 12, 7
-- EXAMPLE 2
/*
INSERT INTO #tmpSelectedMappings( SelectedTypeID, SelectedID )
SELECT 10, 1
UNION ALL
SELECT 11, 6
UNION ALL
SELECT 12, 7
*/
Required Output
What I want is the list of records from #tmpMappings where all the rows from #tmpSelectedMappings exists in the #tmpMappings table.
Note: The table #tmpSelectedMappings is linked to #tmpMappings by columns MappingTypeID, MappingID to SelectedTypeID, SelectedID resp.
Output (Example 1)[ Since all the combination (10,1), (11,4) & (12,7) exists in #tmpMappings]
ReferenceID
1
Output (Example 2) [Since the combination (11,6) is not available in #tmpMappings]
ReferenceID
--Ramesh
-
Maybe:
SELECT D1.ReferenceID
FROM (
SELECT M.ReferenceID, COUNT(M.ReferenceID) AS RefCount
FROM #tmpmappings M
GROUP BY M.ReferenceID
) D1
JOIN (
SELECT M1.ReferenceID, COUNT(M1.ReferenceID) AS RefCount
FROM #tmpmappings M1
JOIN #tmpSelectedMappings S1
ON M1.MappingTypeID = S1.SelectedTypeID
AND M1.MappingID = S1.SelectedID
GROUP BY M1.ReferenceID
) D2
ON D1.ReferenceID = D2.ReferenceID
AND D1.RefCount = D2.RefCount
-
Thanks Ken for your response..Initially, I'd the same thought of using Count as the criteria for filtering the rows..
The query seems like should work for the cases…But, I’m still looking for some creative solutions to the problem…
--Ramesh
-
Umm...
If the selected mappings can be a subset of the mappings in #tmpmappings, then the following may work. If not, I do not see any alternative but to count.
SELECT DISTINCT M.ReferenceID
FROM #tmpmappings M
LEFT JOIN (
SELECT M1.ReferenceID
FROM #tmpmappings M1
LEFT JOIN #tmpSelectedMappings S1
ON M1.MappingTypeID = S1.SelectedTypeID
AND M1.MappingID = S1.SelectedID
WHERE S1.SelectedTypeID IS NULL
) D
ON M.ReferenceID = D.ReferenceID
WHERE D.ReferenceID IS NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply