Version 1: I added an example of exact division
This operation is called relational division. In this case, Table1 (CatID, DealID) is a dividend, and Table2 (CatID) is a dividend. The result (quotient) of the operation Table1(CatID, DealID) DIVIDED BY Table2(CatID) represents all DealIDs (Table 1) that have the same CatIDs (Table 2).
Something is not very clear to me: do you need exact division or division with remainder?
Take a look at this article article written by Joe Selco.
(Revision1) An example for exact division (my values from the table (CatID, DealID) are different, explanations and risks are given below):
DECLARE @Table1 TABLE ( DealID INT NOT NULL ,CatID INT NOT NULL ,PRIMARY KEY(DealID, CatID) ); INSERT @Table1 (DealID, CatID) SELECT src.DealID, src.CatID FROM ( SELECT 1 CatID, 668 DealID UNION ALL SELECT 2 CatID, 668 DealID UNION ALL SELECT 2 CatID, 669 DealID UNION ALL SELECT 1 CatID, 669 DealID UNION ALL SELECT 2 CatID, 671 DealID UNION ALL SELECT 11 CatID, 671 DealID UNION ALL SELECT 12 CatID, 671 DealID UNION ALL SELECT 11 CatID, 672 DealID UNION ALL SELECT 12 CatID, 672 DealID UNION ALL SELECT 13 CatID, 673 DealID ) src;
Explanations:
- This subquery
SELECT CHECKSUM_AGG( CHECKSUM(HASHBYTES('sha1',CAST(b.CatID AS VARCHAR(11)))) ) FROM @Table2 b generates an identifier for all CatID values from the divisor table (Table 2 b). - In the basic query, the records from the dividend table will be grouped (table 1 a):
GROUP BY a.DealID and for each a.DealID value, it generates an identifier for all values from the CatID field using hash functions: CHECKSUM_AGG( CHECKSUM(HASHBYTES('sha1',CAST(a.CatID AS VARCHAR(11)))) ) .
Risks:
- Hashing functions can sometimes create conflicts (especially older hash functions: CHECKSUM [_AGG]).
- To reduce the risk of collisions, I use the HASHBYTES function.
- From my tests (somehow this problem is old for me), I never encountered collisions, but never said never.
- THE DECISION IS PROVIDED "AS IS" WITHOUT WARRANTY.
- For production, use one of Celko's solutions.