CODEWITHSUNDEEP
sqlsql-servert-sqlanalyticsssms
Anthony Ryan
Anthony Ryan

Reputation: 395

SQL: Compare Columns for Exact Match of sets

I have a table that looks like this:

RN    ID1     ID2     Name            Source
1   W76544  945297  1_W_HO          HO
2   W76544  945297  1_W_INT         Int
1   W76547  945299  3_W_HO          HO
2   W76547  945678  3_W_INT         Int
1   W76561  NULL    Dev_U_W         AD
2   W76561  207283  Dev_W_HO        HO
3   W76561  207283  Dev_W_INT       Int
1   W76562  207284  Dev_R_HO        HO
2   W76562  207284  Dev_R_INT       Int
3   W76562  NULL    Dev_U_R         AD
1   W76563  NULL    Prd_U_W         AD
2   W76563  NULL    Prd_W_HO        HO
3   W76563  NULL    Prd_W_INT       Int

I am trying to figure our how to determine exact matches between sets of ID1 and ID2. For instance, this example is an exact match: 

RN    ID1     ID2     Name            Source
1   *W76544 945297* 1_W_HO          HO
2   *W76544 945297* 1_W_INT         Int

I would like the results to look like this:

RN    ID1     ID2  Matched   Name            Source
1   W76544  945297   Yes    1_W_HO          HO
2   W76544  945297   Yes    1_W_INT         Int
1   W76547  945299   No     3_W_HO          HO
2   W76547  945678   No     3_W_INT         Int
1   W76561  NULL     No     Dev_U_W         AD
2   W76561  207283   No     Dev_W_HO        HO
3   W76561  207283   No     Dev_W_INT       Int
1   W76562  207284   No     Dev_R_HO        HO
2   W76562  207284   No     Dev_R_INT       Int
3   W76562  NULL     No     Dev_U_R         AD
1   W76563  NULL     Empty  Prd_U_W         AD
2   W76563  NULL     Empty  Prd_W_HO        HO
3   W76563  NULL     Empty  Prd_W_INT       Int

To claify... Match = 'Yes' when all groups with the same ID1 match up to groups that all have the same ID2. Match = 'No' when Groups with the same ID1 either don't all match up to a like ID2 or some ID1's match but other's in the set do not match to an ID2 at all. Match = 'Empty' when all groups with the same ID1 don't match up to an ID2 at all.

p.s. RN is row_number partitioned by and ordered by ID1

Thanks!!!

@BaconBits , this seems to fall through your query: 

1   W10151820   NULL    No  DEV_U_W         AD
2   W10151820   212405  Yes DEV_W_HO    HO
3   W10151820   212405  Yes DEV_W_INTL  Int

Upvotes: 3

Views: 1714

Answers (5)

Shintaro Sasaki
Shintaro Sasaki

Reputation: 128

How about this one? Could you try after replacing 'YourTableNameHere' with your table name?

WITH
  A AS (
    SELECT
      X.ID1, X.ID2,
      COUNT(1) AS Hits
    FROM YourTableNameHere X
    GROUP BY X.ID1, X.ID2
    HAVING COUNT(1) > 1
  ),
  B AS (
    SELECT
      X.ID1,
      SUM(CASE WHEN X.ID2 IS NULL THEN 0 ELSE 1 END) AS Hits,
      SUM(CASE WHEN X.ID2 IS NULL THEN 1 ELSE 0 END) AS Nulls
    FROM YourTableNameHere X
    GROUP BY X.ID1
    HAVING COUNT(1) > 1
  )
SELECT
  X.RN, X.ID1, X.ID2,
  CASE
    WHEN A.Hits = B.Nulls THEN 'Empty'
    WHEN A.Hits = B.Hits + B.Nulls THEN 'Yes'
    ELSE 'No'
  END AS [Matched],
  X.[Name], X.[Source]
FROM
  YourTableNameHere X
  LEFT JOIN A
    ON
      A.ID1 = X.ID1
      AND (A.ID2 = X.ID2 OR (A.ID2 IS NULL AND X.ID2 IS NULL))
  LEFT JOIN B ON B.ID1 = X.ID1 ;

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try with this:

Create table t(id int, c char(1))

Insert into t values
(1, 'a'),
(1, 'a'),
(2, 'b'),
(2, null),
(3, null),
(3, null),
(4, 'c'),
(4, 'd')

;with cte as(
select id, count(*) c1, count(c) c2, count(distinct c) c3 from t
group by id)
select t.id, t.c, ca.m from t
Cross apply(select case when c2 = 0 and c3 = 0 then 'empty'
            when c1 = c2 and c3 = 1 then 'yes'
            else 'no' end as m
            from cte where cte.id = t.id) ca

Output:

id  c       m
1   a       yes
1   a       yes
2   b       no
2   (null)  no
3   (null)  empty
3   (null)  empty
4   c       no
4   d       no

Upvotes: 3

Yan Brunet
Yan Brunet

Reputation: 4897

To keep things clean, light and easy to understand in the future I would use a CTE to calculate the Matched parameter.

I would then join the results together.

Working example : link

with CTE_CheckID as ( Select ID1,  [Matched] = 
CASE WHEN count(distinct ID2) = 1 AND count(distinct coalesce(id2, id1)) = 1 THEN 'Yes' ELSE
CASE WHEN count(distinct ID2) = 0 AND count(distinct coalesce(id2, id1)) = 1 THEN 'Empty' ELSE 'No' END END
FROM TABLE1
group by ID1 )

select t1.RN, 
  t1.ID1, 
  t1.ID2, 
  cID.Matched,
  t1.name,
  t1.Source
from table1 t1
left join CTE_CheckID cID on t1.ID1 = cID.ID1

Upvotes: 2

Bacon Bits
Bacon Bits

Reputation: 32170

Can you do something like this:

SELECT m.RN,
    m.ID1,
    m.ID2,
    CASE WHEN NOT EXISTS (SELECT 1 FROM MyTable WHERE ID1 = m.ID1 AND ID2 IS NOT NULL) THEN 'Empty'
        ELSE CASE WHEN COUNT(*) OVER(PARTITION BY m.ID1, m.ID2) > 1 THEN 'Yes' 
                ELSE 'No'
            END
    END "Matched",
    m.Name,
    m.Source
FROM MyTable m

Ugly as sin, but I think that should work.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think this may be easier with analytic functions:

select id1, id2,
       (case when cnt_1 = cnt2 and min_1_2 = max_1_2 and min_2_1 = max_2_1 and
                  cnt_1 = cnt_2_notnull
             then 'Yes'
             when cnt_2_notnull > 0
             then 'No'
             else 'Empty'
        end) as flag
from (select t.id1, t.id2,
             count(id1) over (partition by id2) as cnt_2_notnull,
             count(*) over (partition by id1) as cnt_1,
             count(*) over (partition by id2) as cnt_2,
             min(id1) over (partition by id2) as min_1_2,
             max(id1) over (partition by id2) as max_1_2,
             min(id2) over (partition by id1) as min_2_1,
             max(id2) over (partition by id1) as max_2_1
      from table t
     ) t;

The logic is counting the number of values along each dimensions (for id1 and id2) and comparing the minimum and maximum values along each dimension.

Upvotes: 1

Related Questions