Re: Extend inner join to fetch not yet connected rows also - Mailing list pgsql-general

Hi Jan,

> On 22-Sep-2019, at 5:38 PM, Jan Kohnert <nospam001-lists@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
>


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)



>