SQL to compare 2 joins to the same table-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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'
    )
;