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

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!

CodePudding user response：

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