SQL to compare 2 joins to the same table

user_925913 0
Viewed 43

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.

count distinct join group-by sql
2 Answers

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.

user_104988280

Assuming Microsoft SQL Server; it shouldn't be too hard to adapt if you're using a different DBMS:

SELECT
    id, ...
FROM
    users As u
WHERE
    Exists
    (
        SELECT 1
        FROM mappings As m
        INNER JOIN account_details As a
        ON a.id = m.account_id
        WHERE m.user_id = u.id
        AND a.status = 'ACTIVE'
    )
AND
    Exists
    (
        SELECT 1
        FROM mappings As m
        INNER JOIN account_details As a
        ON a.id = m.account_id
        WHERE m.user_id = u.id
        AND a.status = 'SUSPENDED'
    )
;
user_1243860
Related