SQL question
-
My brain is SQL-fried
Here's the DML that creates the data for my question
DROP TABLE #tblParent
DROP TABLE #tblChild
CREATE TABLE #tblParent (id int,ListName varchar(10))
INSERT INTO #tblParent (id,ListName) VALUES (1,'List 1')
INSERT INTO #tblParent (id,ListName) VALUES (2,'List 2')
INSERT INTO #tblParent (id,ListName) VALUES (3,'List 3')
CREATE TABLE #tblChild (ParentId int,CategoryCode char(3))
INSERT INTO #tblChild (ParentId,CategoryCode) VALUES (1,'A')
INSERT INTO #tblChild (ParentId,CategoryCode) VALUES (1,'B')
INSERT INTO #tblChild (ParentId,CategoryCode) VALUES (1,'B')
INSERT INTO #tblChild (ParentId,CategoryCode) VALUES (1,'C')
INSERT INTO #tblChild (ParentId,CategoryCode) VALUES (2,'A')
INSERT INTO #tblChild (ParentId,CategoryCode) VALUES (2,'A')
INSERT INTO #tblChild (ParentId,CategoryCode) VALUES (3,'A')
I'm trying to find the lists that are in both category 'A' and category 'B' - the problem is that categories repeat within the same parent id (notice how parent id 1 has repeating 'B's and 2 has repeating A's (I know its bad normalization, but that's the data I've been given)
Here's the query that doesnt work
SELECT ListName
FROM #tblParent
WHERE id in ( SELECT ParentId
FROM #tblChild
WHERE CategoryCode in ('A','B')
GROUP BY ParentId,CategoryCode
HAVING COUNT(*)=2
)
The query should only return List #1
Thanks
Thanks,Rick Hodder
-
How about:
SELECT ListName
FROM #tblParent p
JOIN #tblChild c
ON p.id = c.ParentId
WHERE c.CategoryCode = 'A'
AND c.CategoryCode = 'B'
GROUP BY c.ParentId
-SQLBill
-
Tested and working:
Select ListName
FROM #tblParent As p
Inner Join
(
Select ParentID, Count(Distinct CategoryCode) As CountAB
From #tblChild
WHERE CategoryCode in ('A','B')
Group By ParentID
) dt
On p.ID = dt.ParentID
Where dt.CountAB = 2
-
Thanks!
I'll try both of these
Rick
Thanks,Rick Hodder
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply