Thread: Extend inner join to fetch not yet connected rows also
I have craftsmanships table which has (id, name) and users table (id, email, ..). When a user has some craftsmanships,
theyare stored inside the contractor_skills(user_id, craftsmanship_id, id) table.
What I want here is that to list all the available craftsmanships with id, name and has column. I can get now only
thosecraftsmanships that a specific user has,
SELECT
craftsmanships.id,
craftsmanships.name,
TRUE as has
FROM
"craftsmanships"
INNER JOIN "contractor_skills" ON "contractor_skills"."craftsmanship_id" = "craftsmanships"."id"
INNER JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
WHERE (contractor_skills.user_id = 8)
ORDER BY
"craftsmanships".”id"
——————
id | name | has
----+---------+-----
1 | paint | t
2 | drywall | t
(2 rows)
But I want to list all craftsmanships and has column should have `t` when user_id #8 has it, else `f`. How can I extend
this query?
Thanks,
Arup Rakshit
ar@zeit.io
Hey,
Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit:
> I have craftsmanships table which has (id, name) and users table (id, email,
> ..). When a user has some craftsmanships, they are stored inside the
> contractor_skills(user_id, craftsmanship_id, id) table.
[...]
> But I want to list all craftsmanships and has column should have `t` when
> user_id #8 has it, else `f`. How can I extend this query?
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;
--
MfG Jan
Hi Jan,
I was close and came up with:
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 = 3
OR contractor_skills.user_id IS NULL)
ORDER BY
"craftsmanships"."id”;
But after I read yours I found mine is doing lot of unnecessary joins. Thank you.
Thanks,
Arup Rakshit
ar@zeit.io
> On 22-Sep-2019, at 5:38 PM, Jan Kohnert <nospam001-lists@jan-kohnert.de> wrote:
>
> Hey,
>
> Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit:
>> I have craftsmanships table which has (id, name) and users table (id, email,
>> ..). When a user has some craftsmanships, they are stored inside the
>> contractor_skills(user_id, craftsmanship_id, id) table.
>
> [...]
>
>> But I want to list all craftsmanships and has column should have `t` when
>> user_id #8 has it, else `f`. How can I extend this query?
>
> 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;
>
> --
> MfG Jan
>
>
>
>
Hi Jan, > On 22-Sep-2019, at 5:38 PM, Jan Kohnert <nospam001-lists@jan-kohnert.de> wrote: > > Hey, > > Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit: >> I have craftsmanships table which has (id, name) and users table (id, email, >> ..). When a user has some craftsmanships, they are stored inside the >> contractor_skills(user_id, craftsmanship_id, id) table. > > [...] > >> But I want to list all craftsmanships and has column should have `t` when >> user_id #8 has it, else `f`. How can I extend this query? > > 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; > > -- > MfG Jan > But this query fetched duplicate data: id | name | has ----+---------------------------------------+----- 2 | drywall | t 1 | paint | t 1 | paint | f 11 | landscaping | f 12 | electrical | f 10 | countertops | f 13 | plumbing | f 5 | flooring | f 8 | decks (displayed as decks and patios) | f 6 | basements | f 4 | kitchens | f 3 | bathrooms | f 14 | handyman | f 9 | windows (windows and doors) | f 7 | carpentry | f (15 rows) >
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:
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
> 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.
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
>
>
>
>
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
On Sun, Sep 22, 2019 at 6:30 AM Arup Rakshit <ar@zeit.io> wrote:
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.
If you do not understand the query - then it's wrong on its face. You should never run something which you do not understand.
So one should take a step back - make smaller pieces and then combine smaller pieces of logic together to form an answer. If at some point in the future there is a performance issue - then deal with that then - but do not make some fancy multi join query that you do not fully understand.
So in that vein,
Piece 1 = A list of craftsmanship_id for a particular user
Piece 2 - Take piece 1 and compare to the full list of craftsmanship_id
Putting piece 1 into a CTE you end up with something like this.
with UserSkills as (
SELECT
craftsmanship_id
FROM
contractor_skills
WHERE
user_id = 3
)
SELECT
CASE WHEN UserSkills.ctraftsmanship_id IS NULL THEN FALSE
ELSE TRUE as has
FROM
craftsmanships
LEFT JOIN
UserSkills
ON
craftsmanships.id = UserSkills.craftsmanship_id
So you take the two pieces and combine then. Yes you can drop the CTE into the main body - but unless you are certain you are doing it correctly - there is no point doing that. The query parser will do the work for you - so why bother making your life more difficult then it need be.
John W Higgins
Hi Arup, Am Sonntag, 22. September 2019, 15:30:38 CEST schrieb Arup Rakshit: > 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. depending on the definition of table "contractor_skills" it can give you a n:m relationship between user_id and craftmanship_id, that is true. > 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”; BUT: you don't use any of users' columns in select, where, or order by. And since users is in a left join it is just a table which is neither used nor relevant in that particular statement. In the end, it depends on how data is structured in your database and what you want to achieve. -- MfG Jan
Just a side note, that the below can be written more simply-
CASE WHEN UserSkills.craftsmanship_id IS NULL THEN FALSE
ELSE TRUE as has
If you want to be direct-
UserSkills.craftsmanship_id IS NOT NULL as has