PostgreSQL - Join three tables and add conditions?

Question

I've got three tables in a PostgreSQL db that looks like this: https://i.sstatic.net/1bNke.jpg

One user can belong to many projects, and one project can have many users, and I'm tying it together through a joined table called "userprojects".

Each table could look like this:

User

| id | firstname | lastname | email          |
|----|-----------|----------|----------------|
| 1  | Joe       | Green    | [email protected]  |
| 2  | Olle      | Svensson | [email protected] |
| 3  | Erik      | Yapp     | [email protected]  |

Project

| id | name          | owner          |
|----|---------------|----------------|
| 1  | Project X     | [email protected]  |
| 2  | Peanut Butter | [email protected] |
| 3  | Apollo 11     | [email protected]  |
| 4  | RCPP          | [email protected]  |

Userprojects

| id | user_id | project_id |
|----|---------|------------|
| 1  | 1       | 1          |
| 2  | 1       | 2          |
| 3  | 2       | 3          |
| 4  | 3       | 3          |

Is there some form of inner(?) join that let's me query on users in a project (eg user_id found in userprojects) OR if the user is an owner of a project?

With the example above, an inner join query that looks like this:

SELECT "project".id, "project".name, "email" 
FROM userprojects 
INNER JOIN project ON userprojects.project_id = project.id 
INNER JOIN "user" ON userprojects.user_id = "user".id

would return this:

| id | name          | email          |
|----|---------------|----------------|
| 1  | Project X     | [email protected]  |
| 2  | Peanut Butter | [email protected]  |
| 3  | Apollo 11     | [email protected] |
| 4  | Apollo 11     | [email protected]  |

What I wish to add to the query result is also the owner of each project if they are not found in that inner join query - notice that [email protected] is the owner of project RCPP but since that relation is not found in the userprojects table, it won't be returned in the query. Can I somehow also get my query to return those users, eg:

| id  | name          | email          |
|-----|---------------|----------------|
| 1   | Project X     | [email protected]  |
| 2   | Peanut Butter | [email protected]  |
| 3   | Apollo 11     | [email protected] |
| 4   | Apollo 11     | [email protected]  |
| (?) | RCPP          | [email protected]  |

forpas · Accepted Answer · 2019-09-03 09:12:57Z

1

Start the joins with the table Project and turn them to LEFT joins:

SELECT 
 "project".id, "project".name, 
 COALESCE("User"."email", "project"."owner") "owner" 
FROM project
LEFT JOIN userprojects  ON userprojects.project_id = project.id 
LEFT JOIN "User" ON userprojects.user_id = "User".id

See the demo.
Results:

| id  | name          | owner          |
| --- | ------------- | -------------- |
| 1   | Project X     | [email protected]  |
| 2   | Peanut Butter | [email protected]  |
| 3   | Apollo 11     | [email protected] |
| 3   | Apollo 11     | [email protected]  |
| 4   | RCPP          | [email protected]  |

answered Sep 3, 2019 at 9:12

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

erikvm Over a year ago

Hmm.. this changes the structure of the query - if a user_id is added to a project in userprojects (even if that user is not the owner), the query will show that added user as the owner

forpas Over a year ago

Your code contains "email" in the results and I kept it. Is this that you want? Or just project.owner?

erikvm Over a year ago

So, if you try to add user 3 to project 2 in the userprojects table - I'd like that to appear in the query as well - even though [email protected] is not the owner of project Peanut Butter

erikvm Over a year ago

Oh, you're right, it does, I can't get it to work in my local postgres DB though - could it have to do something with not showing the unique values? The db-fiddle looks correct but when I do it in the terminal I only get the unique ids seeing as I've got the id in the project table set to unique

Omari Celestine · Accepted Answer · 2019-09-03 10:05:01Z

0

You can create a second INNER JOIN with the table user but this

time matching the email column of the user table to the owner of the project table.

SELECT p.id, P.name, email, CONCAT(u2.firstname, " ", u.lastname) as owner
FROM userprojects up
INNER JOIN project p ON(up.project_id = p.id)
INNER JOIN user u ON(up.user_id = u.id)
INNER JOIN user u2 ON(u2.email = p.owner)

answered Sep 3, 2019 at 10:05

Omari Celestine

1,4351 gold badge14 silver badges21 bronze badges

2 Comments

erikvm Over a year ago

The thing is, I wish to add all users to the result - if they are an owner of the project or their user_id exists in the userprojects table

Omari Celestine Over a year ago

@erikvm So if a user is the owner of a project and they are a member of another project, their name would appear twice?

Collectives™ on Stack Overflow

PostgreSQL - Join three tables and add conditions?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related