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