On 9/22/19 6:30 AM, Arup Rakshit wrote:
On 22-Sep-2019, at 6:55 PM, Jan Kohnert <nospam001-lists@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx>
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.klaver@xxxxxxxxxxx
