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

Hi Arup,

Am Sonntag, 22. September 2019, 15:30:38 CEST schrieb Arup Rakshit:
> When someone adds a craftsmanship to their skill set, the contractor_skills
> table holds that relationship. I don’t think distinct is the correct tool,
> as it will eliminate the correct data. users and craftsmanship has m:n
> relationship via the join table contractor_skills.

depending on the definition of table "contractor_skills" it can give you a n:m
relationship between user_id and craftmanship_id, that is true.

> 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”;

BUT: you don't use any of users' columns in select, where, or order by. And
since users is in a left join it is just a table which is neither used nor
relevant in that particular statement.

In the end, it depends on how data is structured in your database and what you
want to achieve.

--
MfG Jan