How can you display values ​​where the Role_id combination (1,2,3,4) for r_atr_id is observed

Test chart

Tried to do so

Select * from Table where Role_id in (1,2,3,4)

but leads everyone

  • Show what should be the result? I do not understand what it means to "observed the combination" - Viktorov
  • let's say a request came in for the column Role_id, where it is 1,2,3,4. And r_atr_id must be searched for where the numbers are. As if r_atr_id is a variant of the combination. The result is obtained with this combination should bring r_atr_id = 1 - Dauchara
  • I still did not understand anything. Complete the question with the correct output and a more accurate explanation. - Viktorov
  • In this table, two options (r_atr_id) with combinations (1,2,3,4) and (1,2,3). When entering data (1,2,3,4), he should display the first option and when entering (1,2,3) the second option. Those. output in the first case r_atr_id - 1, and in the second 2 - Dauchara

1 answer 1

 SELECT r_atr_id FROM ( SELECT tt.r_atr_id, STUFF((SELECT ',' + t.Role_id FROM [Table] t WHERE t.id = tt.id ORDER BY t.Role_id FOR XML PATH('')), 1, 1, '') comb FROM [Table] tt GROUP BY tt.r_atr_id, tt.Role_id ORDER BY 1 ) WHERE comb = '1,2,3,4';