> On 22-Sep-2019, at 6:55 PM, Jan Kohnert <[email protected]>
> 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 <[email protected]>
>>> 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
>
>
>
>
