getting mutual values of all groups in a table (SQL Server)

Viewed 105

i have a table with 3 columns

id(primary key), masCode (masterCode) and detCode(detailCode)

i need to select detCodes that are repeated in all masCode groups

for example if i have this data:

+----+---------+----------+
| id | masCode | detCode  |
+----+---------+----------+
|  1 | 111     |   1      |
|  2 | 111     |   5      |
|  3 | 222     |   2      |
|  4 | 222     |   5      |
|  5 | 222     |   1      |
|  6 | 223     |   5      |
|  7 | 223     |   6      |
|  8 | 223     |   1      |
+----+---------+----------+

the result will be this:

+----+---------+----------+
| id | masCode | detCode  |
+----+---------+----------+
|  1 | 111     |   1      |
|  2 | 111     |   5      |
|  4 | 222     |   5      |
|  5 | 222     |   1      |
|  6 | 223     |   5      |
|  8 | 223     |   1      |
+----+---------+----------+

i think i can do this with dynamic Query, is there any better way to do this? thanks

4 Answers
Related