| From: | Arup Rakshit <ar(at)zeit(dot)io> |
|---|---|
| To: | Jan Kohnert <nospam001-lists(at)jan-kohnert(dot)de> |
| Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Extend inner join to fetch not yet connected rows also |
| Date: | 2019-09-22 12:12:42 |
| Message-ID: | 3B4F89A5-22AA-4975-A156-C59E23F12378@zeit.io |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
ar(at)zeit(dot)io
> On 22-Sep-2019, at 5:38 PM, Jan Kohnert <nospam001-lists(at)jan-kohnert(dot)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
>
>
>
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Arup Rakshit | 2019-09-22 12:48:20 | Re: Extend inner join to fetch not yet connected rows also |
| Previous Message | Jan Kohnert | 2019-09-22 12:08:53 | Re: Extend inner join to fetch not yet connected rows also |