I have a table master_account like so

and another table affiliate_partners like so

I'm logging in my application as account_id 3067261 and I get to see a list of all the accounts in master_account except for account_id 3067261 which is mine. At the same time I need to see the statuses(is_client, is_driver) for my account_id 3067261 which are in affiliate_partners related to my account_id. Till now, I have tried this query but it returns null for all the statuses(is_client, is_driver) and I do need null for the account_id for those who aren't connected in the affiliate_partners table.

SELECT 
        ma._id,
        ma.account_id, 
        CASE 
        WHEN ma.account_id = '3067261'
        THEN ap.is_client
        ELSE null
        END as is_client,
        CASE 
        WHEN ma.account_id = '3067261'
        THEN ap.is_driver
        ELSE null
        END as is_driver
from master_account ma
left join affiliate_partners ap
on ma.account_id = ap.account_id
where ma.account_id != '3067261'

Expected result for the above sample:

But instead I get:

CodePudding user response：

Seems you need a predicate in the join condition of your LEFT JOIN:

SELECT ma.*, ap.is_client, ap.is_driver
FROM   master_account ma
LEFT   JOIN affiliate_partners ap ON ma.account_id = ap.account_id
                                 AND ap.partner_account_id = '3067261'  -- !!
WHERE  ma.account_id <> '3067261';

This way, you also don't need CASE expressions. is_client and is_driver are NULL if there is no related row in affiliate_partners with partner_account_id = '3067261'

See:

• Query with LEFT JOIN not returning rows for count of 0
• Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail

CodePudding user response：

you need to use JOIN that would allow you to combine the tables and get the data you want depending on your where condition