Return result for one-to-many given table B ID

userWqDl1 0
Viewed 27

I know this has probably been answered before, but I apologize as I cannot find the answer that might solve this for me. I have a one-to-many Postgres database where I want to make sure that every row in table A is in the result, as well as a value that is stored in table B, and make sure to also show table A even if table B does not have an entry that links to a row in table A.

Table A:

tableA_id name
1 Row1
2 Row2
3 Row3
4 Row4

Table B:

tableB_id a_id user_id is_subscribed
1 1 1 true
2 1 2 false
3 2 1 true
4 3 1 false
5 4 3 true

I want a query where I can return the table A entries for each specific user, along with the is_subscribed value for each entry, even those which there is not an entry in table B. SO the results would look like this:

(given user Id 1):

tableA_id name is_subscribed
1 Row1 true
2 Row2 true
3 Row3 false
4 Row4 null

I have been able with JOIN tables to get it very close, but I am struggling past the hurdle of returning the 4th row in table A as there is not an entry in table B.

Thanks!

sql postgresql
1 Answers

If when you use inner join (join is default - inner join) you will be see this result:

tableA_id   name    is_subscribed
1           Row1    true
2           Row2    true
3           Row3    false

If when you use left join you will be see this result:

tableA_id   name    is_subscribed
1           Row1    true
2           Row2    true
3           Row3    false
4           Row4    null

And if you want to see default false when data is not in table_b use this query:

select 
    a.tableA_id, 
    a.name, 
    case when b.is_subscribed is null then false else b.is_subscribed end as is_subscribed 
from 
    table_A a
left join 
    table_B b on b.a_id = a.tableA_id and b.user_id = 1


Result: 
tableA_id   name    is_subscribed
1           Row1    true
2           Row2    true
3           Row3    false
4           Row4    false
Ramin Faracov0
Related