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

> On 22-Sep-2019, at 6:55 PM, Jan Kohnert <nospam001-lists@jan-kohnert.de> wrote:
>
> Hi Arup,
>
> Am Sonntag, 22. September 2019, 14:48:20 CEST schrieb Arup Rakshit:
>> Hi Jan,
>>
>>> On 22-Sep-2019, at 5:38 PM, Jan Kohnert <nospam001-lists@jan-kohnert.de>
>>> wrote:
>>> 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;
>>
>> But this query fetched duplicate data:
>
> yeah, that's possible, since I don't exactly know your data model. If only the
> values above are required, you could simply use distinct:

When someone adds a craftsmanship to their skill set, the contractor_skills table holds that relationship. I don’t
thinkdistinct is the correct tool, as it will eliminate the correct data. users and craftsmanship has m:n relationship
viathe join table contractor_skills. 

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

Gives correct result. Not sure if still this query has bug in it.

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




>
> select distinct
>    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.id
> order by
>   c.id;
>
> --
> MfG Jan
>
>
>
>