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