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