Search code examples
sqljoingroup-bycountdistinct

SQL to compare 2 joins to the same table

I have a table that maps accounts to users. Accounts can have multiple users. User can be in multiple accounts.

account_id  | user_id 
------------+---------
1234          a
1234          b
5678          c
6789          a

For example, here user "a" is in accounts 1234 and 6789.

I have another table with account details.

account_id  | status     | ...
------------+------------+-----
1234          ACTIVE
5678          ACTIVE
6789          SUSPENDED

I want to know all the users who are ACTIVE in one account while SUSPENDED in another account. (Any mix, if they're in more than 2 accounts.) In the above example, user "a" is ACTIVE in 1234 and SUSPENDED in 6789.

My attempt started as...

SELECT user_id FROM mappings
LEFT JOIN account_details AS x ON account_id = x.id AND x.status = 'ACTIVE'
LEFT JOIN account_details AS y ON account_id = y.id AND y.status = 'SUSPENDED'

But that seems wrong and I don't know how I'd ensure the 2 joins are linked to the same user. There must be a different way to approach this problem that I'm not seeing.

Thanks for any hints.

Solution

  • You can join the tables to aggregate by user and set the condition in the HAVING clause:

    SELECT m.user_id
FROM mappings m INNER JOIN account_details a
ON a.account_id = m.account_id
WHERE a.status IN ('ACTIVE', 'SUSPENDED')
GROUP BY m.user_id
HAVING COUNT(DISTINCT a.status) = 2;

    If 'ACTIVE' and 'SUSPENDED' are the only possible values of the column status then you can omit the WHERE clause.

    See the demo.