Get all rows with a matching field in a different row in the same table

Question

Let's say I have a table like this:

|id|userID|email         |website    |
--------------------------------------
|1 |user1 |[email protected]|website.com|
|2 |user2 |[email protected]|website.com|
|3 |user3 |[email protected]|website.com|
|4 |user1 |[email protected]|foo.com    |
|5 |user2 |[email protected]|foo.com    |

And I want to get all of the rows where website='website.com' and have a corresponding row with a matching userID where website='foo.com'

So, in this instance it would return rows 1 and 2.

Any ideas?

Answer 1

To get the user you can do

select userID
from your_table
where website in ('website.com', 'foo.com')
group by userID
having count(distinct website) = 2

but if you need the complete row then do

select * from your_table
where userID in
(
   select userID
   from your_table
   where website in ('website.com', 'foo.com')
   group by userID
   having count(distinct website) = 2
)