Re: Extend inner join to fetch not yet connected rows also
От | Adrian Klaver |
---|---|
Тема | Re: Extend inner join to fetch not yet connected rows also |
Дата | |
Msg-id | f9705107-7f5c-06f6-d0f6-630554bd6891@aklaver.com обсуждение исходный текст |
Ответ на | Re: Extend inner join to fetch not yet connected rows also (Arup Rakshit <ar@zeit.io>) |
Список | pgsql-general |
On 9/22/19 6:30 AM, Arup Rakshit wrote: > >> On 22-Sep-2019, at 6:55 PM, Jan Kohnert <nospam001-lists@jan-kohnert.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@jan-kohnert.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 thinkdistinct is the correct tool, as it will eliminate the correct data. users and craftsmanship has m:n relationship viathe 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@aklaver.com
В списке pgsql-general по дате отправления:
Следующее
От: John W HigginsДата:
Сообщение: Re: Extend inner join to fetch not yet connected rows also