Extend inner join to fetch not yet connected rows also

From: Arup Rakshit <ar(at)zeit(dot)io>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Extend inner join to fetch not yet connected rows also
Date: 2019-09-22 11:21:46
Message-ID: 7B8CBECF-09A5-492B-9E22-EDD7B0DBD469@zeit.io
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have craftsmanships table which has (id, name) and users table (id, email, ..). When a user has some craftsmanships, they are stored inside the contractor_skills(user_id, craftsmanship_id, id) table.

What I want here is that to list all the available craftsmanships with id, name and has column. I can get now only those craftsmanships that a specific user has,

SELECT
craftsmanships.id,
craftsmanships.name,
TRUE as has
FROM
"craftsmanships"
INNER JOIN "contractor_skills" ON "contractor_skills"."craftsmanship_id" = "craftsmanships"."id"
INNER JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
WHERE (contractor_skills.user_id = 8)
ORDER BY
"craftsmanships".”id"

——————
id | name | has
----+---------+-----
1 | paint | t
2 | drywall | t
(2 rows)

But I want to list all craftsmanships and has column should have `t` when user_id #8 has it, else `f`. How can I extend this query?

Thanks,

Arup Rakshit
ar(at)zeit(dot)io

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Kohnert 2019-09-22 12:08:53 Re: Extend inner join to fetch not yet connected rows also
Previous Message Andrew Gierth 2019-09-21 08:21:06 Re: Use of ?get diagnostics'?