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

[Arup Rakshit]

Hi Jan,

I was close and came up with:

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 = 3
        OR contractor_skills.user_id IS NULL)
ORDER BY
        "craftsmanships"."id”;

But after I read yours I found mine is doing lot of unnecessary joins. Thank 
you.


Thanks,

Arup Rakshit
a...@zeit.io



> On 22-Sep-2019, at 5:38 PM, Jan Kohnert <nospam001-li...@jan-kohnert.de> 
> wrote:
> 
> Hey,
> 
> Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit:
>> 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.
> 
> [...]
> 
>> 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?
> 
> maybe something like
> 
> select
>    c.id,
>    c.name,
>    case when cs.user_id = 8 then true else false end as has
> from craftsmanships c
> left join contractor_skills cs
>    on cs.craftsmanship_id = c.craftmanship_id;
> 
> -- 
> MfG Jan
> 
> 
> 
>