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

On 9/22/19 6:30 AM, Arup Rakshit wrote:
>
>> On 22-Sep-2019, at 6:55 PM, Jan Kohnert <nospam001-lists(at)jan-kohnert(dot)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(at)jan-kohnert(dot)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 think distinct is the correct tool, as it will eliminate the correct data. users and craftsmanship has m:n relationship via the 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.

What I see is that the rows below with 'has' = 'f' will not have a
user_id(implied). So I am not sure how you plan to associate that data
with a user?

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com