Comparing sets of rows in the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing sets of rows in the same table

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-06-26 : 16:18:38

Ok, I'm stumped. Maybe it's an easy one, but I can't see it. I searched the FAQ's as well, so feel free to point me in the right direction if I missed it.

Give the following table:


DECLARE @compare TABLE
(Store INT
,Item VARCHAR(3)
,Quantity INT)

INSERT @compare VALUES(1, 'A', 20)
INSERT @compare VALUES(1, 'B', 50)
INSERT @compare VALUES(2, 'A', 50)
INSERT @compare VALUES(2, 'C', 30)
INSERT @compare VALUES(3, 'A', 20)
INSERT @compare VALUES(3, 'B', 50)
INSERT @compare VALUES(4, 'A', 20)
INSERT @compare VALUES(5, 'A', 50)
INSERT @compare VALUES(5, 'C', 30)

SELECT * FROM @compare

I need to find out which stores have the exact same set of Item-Quantity values. In the above data, for instance, I would have 3 groups: stores 1 & 3, stores 2 & 5, and store 4. Any ideas on a good way to do this? It was even super-complicated using cursors.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-26 : 16:28:52

SELECT A.Store, B.Store, A.Item, A.Quantity
FROM @compare A INNER JOIN @compare B
ON A.Store<>B.Store AND A.Item=B.Item AND A.Quantity=B.Quantity

That should work.

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-06-26 : 16:35:56

Try something like this:

SELECT *
FROM @Compare a
WHERE EXISTS
(
SELECT 1
FROM @Compare b
WHERE a.Store <> b.Store
AND a.Item = b.Item
AND a.Quantity = b.Quantity
)

Untested -- Don't have SQL2K handy to test.

--
HTH,
Vyas
http://vyaskn.tripod.com

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-06-26 : 16:40:43


SELECT A.Store, B.Store, A.Item, A.Quantity 
FROM @compare A INNER JOIN @compare B 
ON A.Store<>B.Store AND A.Item=B.Item AND A.Quantity=B.Quantity

Close, but not quite.

The main problem is the entry for store #4. I only want stores to match up if the entire set of Item-Quantity pairs match. Subsets don't count. In your code store #4 is showing up matched to both stores 1 and 3, but it shouldn't because it doesn't also have an item B with quantity of 50 like the other two do.

It's a good start though, maybe I can do something with this code. Any other ideas in the meantime?

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources