Join Fragment CTE to construct our proper table

Question

This is my query

with cte as ( 
select  mail 
from dbo.reseau

where   mail  in       
 ( select   mail   from dbo.reseau
 group by mail
 having count(1) > 1
  ) ) 
  select * from (
  
  select  trig 
from dbo.reseau

where   trig  in      
 ( select   trig   from dbo.reseau
 group by trig
 having count(1) > 1
  ))t
  select * from 
  ( 
  select nom from reseau 
  where nom  in (
( select   nom   from dbo.reseau
 group by nom
 having count(1) > 1
  ) )  )c
    
  select * from  (
 select prenom from reseau 
  where prenom   in (
( select   prenom    from dbo.reseau
 group by prenom 
 having count(1) > 1)))r

I have 4 columns and I wrote this CTE to show duplicate data. My query shows them separately. I want to join them. How can I do this?

Answer 1

If you want rows where any of the columns contain duplicate values, you can use window functions multiple times:

select *
from (
    select 
        r.*,
        count(*) over(partition by mail) cnt_mail,
        count(*) over(partition by trig) cnt_trig,
        count(*) over(partition by nom) cnt_nom,
        count(*) over(partition by prenom) cnt_prenom
    from reseau
) t
where cnt_mail > 1 or cnt_trig > 1 and cnt_nom > 1 end cnt_prenom > 1

The where clause can be simplified as:

where cnt_email + cnt_trig + cnt_nom + cnt_prenom > 4