December 11, 2009 at 1:45 pm
CREATE TABLE #TempTable
(
GroupName varchar(25),
FieldName varchar(25),
FieldValue varchar(25)
)
--SAMPLE DATA
INSERT INTO #TempTable (GroupName,FieldName,FieldValue)
SELECT 'Group1','FieldA','XYZ' UNION ALL
SELECT 'Group1','FieldB','ABC' UNION ALL
SELECT 'Group2','FieldA','XYZ' UNION ALL
SELECT 'Group2','FieldB','ABC' UNION ALL
SELECT 'Group2','FieldZ','123' UNION ALL
SELECT 'Group3','FieldA','XYZ' UNION ALL
SELECT 'Group3','FieldB','ABC' UNION ALL
SELECT 'Group4','FieldA','456' UNION ALL
SELECT 'Group4','FieldB','ABC' UNION ALL
SELECT 'Group5','FieldA','XYZ'
I want to query for a list of GroupNames that have the exact same set of FieldNames/FieldValues as the group I'm querying for.
So from the sample data above, if I were to query for groups that match Group1, the only result I should get is Group3.
** Group2 is close, but it has an extra FieldZ.
** Group4 is close, but is has a different value for FieldA.
** Group5 is close, but it has no FieldB.
December 11, 2009 at 2:29 pm
Try this:
SELECT B.GroupName
FROM test A
INNER JOIN test B ON A.FieldName = B.FieldName
AND A.FieldValue = B.FieldValue
AND A.GroupName <> B.GroupName
WHERE A.GroupName = 'Group1'
AND B.GroupName NOT IN (
SELECT B.GroupName
FROM test A
RIGHT OUTER JOIN test B ON A.FieldName = B.FieldName
AND A.FieldValue = B.FieldValue
AND A.GroupName = 'Group1'
WHERE A.GroupName IS NULL
)
GROUP BY B.GroupName
HAVING COUNT(*) = ( SELECT COUNT(*)
FROM test
WHERE GroupName = 'Group1')
It looks ugly but works with the data you provided. It might be improvable but it is late Friday... Brain is shutting down...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 12, 2009 at 10:10 am
Yep, that seems to be working! Thanks ALOT!
December 14, 2009 at 4:20 am
DROP TABLE #TempTable
CREATE TABLE #TempTable
(
GroupName varchar(25),
FieldName varchar(25),
FieldValue varchar(25)
)
--SAMPLE DATA
INSERT INTO #TempTable (GroupName,FieldName,FieldValue)
SELECT 'Group1','FieldA','XYZ' UNION ALL
SELECT 'Group1','FieldB','ABC' UNION ALL
SELECT 'Group2','FieldA','XYZ' UNION ALL
SELECT 'Group2','FieldB','ABC' UNION ALL
SELECT 'Group2','FieldZ','123' UNION ALL
SELECT 'Group3','FieldA','XYZ' UNION ALL
SELECT 'Group3','FieldB','ABC' UNION ALL
SELECT 'Group4','FieldA','456' UNION ALL
SELECT 'Group4','FieldB','ABC' UNION ALL
SELECT 'Group5','FieldA','XYZ'
;WITH CTE AS
(
SELECT
GroupName,
(
SELECT
''+FieldName+FieldValue
FROM
#TempTable t1
WHERE
T1.GroupName = T.GroupName
ORDER BY
FieldName,FieldValue
FOR XML PATH('')
) AS CompareValue
FROM
#TempTable T
GROUP BY
GroupName
)
SELECT A.GroupName FROM CTE A INNER JOIN CTE B
ON
A.CompareValue = B.CompareValue AND
A.GroupName <> B.GroupName
Regards,
Mitesh OSwal
+918698619998
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply