Question 115085 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql join query matching null

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    JOIN clause and WHERE clause does the same things but logicaly differs: JOIN clause uses for jointing tables but WHERE clause uses for filtering records.

    So the condition settings.name = ‘setting1’ need to turn into into WHERE clause for claricity. Also you can comment out WHERE clause for troubleshooting.

    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search