-2

I have a table master_account like so

_id account_id
1 3067261
2 4327735
3 8521420

and another table affiliate_partners like so

_id account_id partner_account_id is_client is_driver
1 3067261 4327735 true true
2 4327735 3067261 true true
3 8521420 4327735 false false

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:

_id account_id is_client is_driver
2 4327735 true true
3 8521420 false false

But instead I get:

_id account_id is_client is_driver
2 4327735 null null
3 8521420 null null
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Deep Mandal
  • 33
  • 1
  • 8
  • 1
    Please provide your expected results for the sample data. – Stu Dec 02 '22 at 09:24
  • This code fragment is broken: `CASE WHEN ap.account_id = ma.account_id` Please, your Postgres version and table definitions (`CREATE TABLE` statements showing data types and constraints). And your explanation does not seem clear, yet. – Erwin Brandstetter Dec 02 '22 at 09:30
  • Apologies, let me fix the query. – Deep Mandal Dec 02 '22 at 09:35
  • What is the question? – philipxy Dec 02 '22 at 09:57
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Dec 02 '22 at 09:57

1 Answers1

0

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.partner_account_id
                                 AND ap.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'
(Also, you had ma.account_id instead of ap.account_id.)

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228