Master:
  | Id  | Name |
  |  0  | Abba |
  |  1  | Abbc |
  |  2  | Abbd |
   .............

Detail:
  | Id  | MId  |
  |  0  |   1  |
  |  1  |   1  |
  |  2  |   0  |
  |  3  |   0  |
  |  4  |   1  |
  |  5  |   0  |
  |  6  |   2  |
   .............

SELECT T1.*, T2.cnt 
FROM Detail AS T1
JOIN
(
  SELECT MId, count(*) AS cnt
  FROM Detail
  GROUP BY MId
) T2
ON T1.MId = T2.MId
WHERE T2.cnt > 2;

D>SELECT T1.*, T2.cnt 
D>FROM Detail AS T1
D>JOIN
D>(
D>  SELECT MId, count(*) AS cnt
D>  FROM Detail
D>  GROUP BY MId
D>) T2
D>ON T1.MId = T2.MId
D>WHERE T2.cnt > 2;
D>

SELECT   mid, COUNT (*) AS cnt
    FROM detail
GROUP BY mid
  HAVING COUNT (*) > 2;

SELECT T1.*, T2.cnt 
FROM Detail AS T1
JOIN
(
  SELECT MId, count(*) AS cnt
  FROM Detail
  GROUP BY MId
  HAVING count(*) > 2
) T2
ON T1.MId = T2.MId;

        create table #table (    kod        int,
                                name    nvarchar(10)
                            )
        insert into #table
        select 1, 'test 1'
        union all
        select 1, 'test 1'
        union all
        select 1, 'test 1'
        union all
        select 2, 'test 2'
        union all
        select 3, 'test 3'
        union all
        select 3, 'test 3'
        union all 
        select 3, 'test 3'

        select    * 
        from    #table leftJoinTable
        right join (    select    kod 
                        from    #table 
                        group    by kod 
                        having count(*) > 2
                    ) rightJoinTable on rightJoinTable.kod = leftJoinTable.kod
        
        drop table #table

1 test 1 1
1 test 1 1
1 test 1 1
3 test 3 3
3 test 3 3
3 test 3 3

select * from Detail d1
where exists (select * from Detail d2 where d1.MId = d2.MId having count(*) > 2)

SELECT t1.Id, t1.MId, count(*) AS cnt
FROM Detail AS t1
JOIN Detail AS t2
ON t1.MId = t2.MId
GROUP BY t1.Id, t1.MId
HAVING count(*)>=2;

D>>SELECT t1.*
D>>FROM Detail AS t1
D>>JOIN Detail AS t2
D>>ON t1.MId = t2.MId
D>>GROUP BY t1.Id, t1.MId
D>>HAVING count(*)>=2;
D>>

SELECT t1.*, count(*) AS cnt

When GROUP BY exists, every simple field in projectors must be in GROUP BY.