Extend inner join to fetch not yet connected rows also

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

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

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 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@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
craftsmanships.id,
craftsmanships.name,
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