On Sun, Sep 22, 2019 at 6:30 AM Arup Rakshit <a...@zeit.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
