postgresql join query matching null-CodePudding

I can't make a request.

I have 3 tables.

I would like to have the following result:

services

id	name
1	service1
2	service2

settings

id	name	default_value
1	setting1	10
2	setting2	false

services_settings

id	service_id	setting_id	value
1	1	1	50

result

service_id	final_value
1	50
2	10

My query :

select
    services.id,
    coalesce(services_settings.value, settings.default_value)
from services
left join services_settings on services_settings.service_id = services.id
left join settings on settings.id = services_settings.setting_id and settings.name = 'setting1'

Thank you

CodePudding user response：

You should use CROSS JOIN here

select 
    services.id,
    coalesce(services_settings.value, settings.default_value)
from services
left join services_settings on services_settings.service_id = services.id
cross join settings 
where settings.name = 'setting1'

SQL online editor

CodePudding user response：

select
    services.id,
    coalesce(services_settings.value, settings.default_value)
from services
left join services_settings on services_settings.service_id = services.id
left join settings on settings.id = services_settings.setting_id 
WHERE settings.name = 'setting1'