title: SQL: Compare Columns for Exact Match of sets
body: <p>I have a table that looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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: </p>
  
  <pre><code>RN    ID1     ID2     Name            Source
  1   *W76544 945297* 1_W_HO          HO
  2   *W76544 945297* 1_W_INT         Int
  </code></pre>
  
  <p>I would like the results to look like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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>
  
  <p>p.s. RN is row_number partitioned by and ordered by ID1</p>
  
  <p>Thanks!!!</p>
  
  <hr>
  
  <p>@BaconBits , this seems to fall through your query: </p>
  
  <pre><code>1   W10151820   NULL    No  DEV_U_W         AD
  2   W10151820   212405  Yes DEV_W_HO    HO
  3   W10151820   212405  Yes DEV_W_INTL  Int
  </code></pre>