I have a table that looks like this:

| id   | email          | name |
| -----| -------------- |------|
| 1    | [email protected]  | joe  |
| 2    | [email protected]  | john |
| 3    | [email protected]  | joe  |
| 4    | [email protected]  | joe  |
| 5    | [email protected]  | dee  |
| 6    | [email protected]  | bri  |

I need to query the table for all rows where the same name has an email in the list "[email protected]", "[email protected]". This expected result would return rows 1 and 4.

CodePudding user response：

You could use a subquery or CTE using INTERSECT to get the name, then join on name to return all columns.

with cte1 as (
   select name from my_data where email = '[email protected]'
   intersect
   select name from my_data where email = '[email protected]'
  )
select d.*
from my_data d
join cte1 c
  on d.name = c.name
where email in('[email protected]', '[email protected]');

Another query with same results:

with cte1 as (
   select name, count(*) as email_count
   from my_data
   where email in ('[email protected]', '[email protected]')
   group by name
  )
select d.*
from my_data d
join cte1 c
  on d.name = c.name
where email in('[email protected]', '[email protected]')
  and c.email_count > 1;

CodePudding user response：

select   id
        ,name
        ,email
from
(
select   *
        ,case when email = '[email protected]' and count(case when email = '[email protected]' then 1 end) over(partition by name) != 0 then 1 end as f
        ,case when email = '[email protected]' and count(case when email = '[email protected]' then 1 end) over(partition by name) != 0 then 1 end as f2
from     t
) t
where    f  > 0
   or    f2 > 0

Fiddle