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

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:48:20
Message-ID: BBC4F372-71F2-46BA-BCF2-CCEB4F7F9F4B@zeit.io
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Jan,

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

But this query fetched duplicate data:

id | name | has
----+---------------------------------------+-----
2 | drywall | t
1 | paint | t
1 | paint | f
11 | landscaping | f
12 | electrical | f
10 | countertops | f
13 | plumbing | f
5 | flooring | f
8 | decks (displayed as decks and patios) | f
6 | basements | f
4 | kitchens | f
3 | bathrooms | f
14 | handyman | f
9 | windows (windows and doors) | f
7 | carpentry | f
(15 rows)

>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Kohnert 2019-09-22 13:25:46 Re: Extend inner join to fetch not yet connected rows also
Previous Message Arup Rakshit 2019-09-22 12:12:42 Re: Extend inner join to fetch not yet connected rows also