Re: Extend inner join to fetch not yet connected rows also

On Sun, Sep 22, 2019 at 6:30 AM Arup Rakshit <ar(at)zeit(dot)io> wrote:

>
>
> SELECT
> craftsmanships.id,
> craftsmanships.name,
> CASE WHEN contractor_skills.user_id IS NULL THEN
> FALSE
> ELSE
> TRUE
> END AS has
> FROM
> "craftsmanships"
> LEFT JOIN "contractor_skills" ON
> "contractor_skills"."craftsmanship_id" = "craftsmanships"."id"
> LEFT JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
> WHERE (contractor_skills.user_id = 8
> OR contractor_skills.user_id IS NULL)
> ORDER BY
> "craftsmanships"."id”;
>
> Gives correct result. Not sure if still this query has bug in it.
>
>
If you do not understand the query - then it's wrong on its face. You
should never run something which you do not understand.

So one should take a step back - make smaller pieces and then combine
smaller pieces of logic together to form an answer. If at some point in the
future there is a performance issue - then deal with that then - but do not
make some fancy multi join query that you do not fully understand.

So in that vein,

Piece 1 = A list of craftsmanship_id for a particular user
Piece 2 - Take piece 1 and compare to the full list of craftsmanship_id

Putting piece 1 into a CTE you end up with something like this.

with UserSkills as (
SELECT
craftsmanship_id
FROM
contractor_skills
WHERE
user_id = 3
)
SELECT
craftsmanships.id,
craftsmanships.name,
CASE WHEN UserSkills.ctraftsmanship_id IS NULL THEN FALSE
ELSE TRUE as has
FROM
craftsmanships
LEFT JOIN
UserSkills
ON
craftsmanships.id = UserSkills.craftsmanship_id

So you take the two pieces and combine then. Yes you can drop the CTE into
the main body - but unless you are certain you are doing it correctly -
there is no point doing that. The query parser will do the work for you -
so why bother making your life more difficult then it need be.

John W Higgins