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 13:30:38
Message-ID: 5C7E04B7-F5E3-4F2C-99A7-A8842153F531@zeit.io
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pankaj Jangid 2019-09-22 14:25:00 How to represent a bi-directional list in db?
Previous Message Jan Kohnert 2019-09-22 13:25:46 Re: Extend inner join to fetch not yet connected rows also