Join Two Tables Conditioned on a Third Table

Question

I am trying to create a view of two tables, based on a condition from a third. I have three tables:

clients

+-----------+------------+
| client_id | first_name |
+-----------+------------+
|  10000000 | Samantha   |
|  10000001 | Andrew     |
|  10000002 | Audrey     |
+-----------+------------+

addresses

+-----------+------------------+---------------+-----------+
| client_id |    account_id    |    street     |   city    |
+-----------+------------------+---------------+-----------+
|  10000000 | AAAAAAAAAAAAAAAA | 123 Apple St  | Brussels  |
|  10000000 | BBBBBBBBBBBBBBBB | 111 Orange St | Hong Kong |
|  10000001 | CCCCCCCCCCCCCCCC | 456 Grape St  | Amsterdam |
|  10000002 | DDDDDDDDDDDDDDDD | 789 Peach St  | Toronto   |
+-----------+------------------+---------------+-----------+

accounts

+-----------+------------------+-----------+
| client_id |    account_id    |  status   |
+-----------+------------------+-----------+
|  10000000 | AAAAAAAAAAAAAAAA | cancelled |   <-- i do not want this record
|  10000000 | BBBBBBBBBBBBBBBB | active    |
|  10000001 | CCCCCCCCCCCCCCCC | active    |
|  10000002 | DDDDDDDDDDDDDDDD | active    |
+-----------+------------------+-----------+

I am trying to achieve an output that looks like:

+-----------+------------+------------------+---------------+-----------+
| client_id | first_name |    account_id    |    street     |   city    |
+-----------+------------+------------------+---------------+-----------+
|  10000000 | Samantha   | BBBBBBBBBBBBBBBB | 111 Orange St | Hong Kong |
|  10000001 | Andrew     | CCCCCCCCCCCCCCCC | 456 Grape St  | Amsterdam |
|  10000002 | Audrey     | DDDDDDDDDDDDDDDD | 789 Peach St  | Toronto   |
+-----------+------------+------------------+---------------+-----------+

I'm having trouble pulling the results. I end up with both records for Samantha. Here's my latest query effort; what am I doing wrong?

SELECT
    ClientTable.client_id,
    ClientTable.first_name,
    AddressTable.account_id,
    AddressTable.street,
    AddressTable.city
FROM
    [clients] ClientTable
    INNER JOIN
    (
        SELECT
            [addresses].client_id,
            [addresses].account_id,
            [addresses].street,
            [addresses].city
        FROM [addresses]
        INNER JOIN [accounts] ON [addresses].client_id = [accounts].client_id
        WHERE [accounts].status <> 'cancelled'
    ) AddressTable ON ClientTable.client_id = AddressTable.client_id

Answer 1

You can join the clients and addresses tables, and use not exists to evict "cancelled" accounts:

select cl.client_id, cl.first_name, ad.account_id, ad.street, ad.city
from clients cl
inner join addresses ad on ad.client_id = cl.client_id
where not exists (
    select 1 
    from accounts ac 
    where 
        ac.client_id = ad.client_id 
        and ac.account_id = ad.account_id
        and ac.status = 'cancelled'
)

Another approach uses a join on accounts, as your originally intended to:

select cl.client_id, cl.first_name, ad.account_id, ad.street, ad.city
from clients cl
inner join addresses ad on ad.client_id = cl.client_id
inner join accounts ac on ac.client_id = ad.client_id and ac.account_id = ad.account_id
where ac.status <> 'cancelled'

Note that both queries do not do exactly the same thing; the second query requires an account with a non-cancelled status, while the first just forbids cancelled account (an address without an account would be allowed).

Demo on DB Fiddle - both queries yield:

client_id | first_name | account_id       | street        | city     
--------: | :--------- | :--------------- | :------------ | :--------
 10000000 | Samantha   | BBBBBBBBBBBBBBBB | 111 Orange St | Hong Kong
 10000001 | Andrew     | CCCCCCCCCCCCCCCC | 456 Grape St  | Amsterdam
 10000002 | Audrey     | DDDDDDDDDDDDDDDD | 789 Peach St  | Toronto