postgresql select where condition in another table

Question

I have three tables

1. project (id, user_id, project_name)
2. project_roles (id, user_id, project_id)
3. users (id, nickname)

I want to fetch all projects for user that has id 1 and I also want to get projects where this user has record in project_roles

So, if user has project A and B and this user has row in project_roles for project C, I want to fetch projects A, B and C

I tried this:

SELECT *
from project
WHERE user_id = 1
   OR (project.id = project_roles.project_id WHERE project_roles.user_id = 1)

example data:

USERS TABLE

1. id: 1, nickname: 'test'

PROJECTS table

1. id: 7777, user_id: 2, project_name: 'NAME1'
2. id: 8888, user_id: 1, project_name: 'NAME2'
3. id: 9999, user_id: 1, project_name: 'NAME3'

PROJECT_ROLES table

1. id: 5, user_id: 1, project_id: '7777'

I want to fetch all 3 projects for user 1 because projects with id 8888 and 9999 has user_id: 1 and there is project_roles data where user_id is 1 so I want to get project 7777 also

Answer 1

Tried to replicate sample data, its a bit different than what you have provided but the context is the same.

Schema (MySQL v8.0)

CREATE TABLE project 
(
    id  INT,
    project_name    VARCHAR(512),
    user_id INT
);

INSERT INTO project (id, project_name, user_id) VALUES ('1', 'A', '23');
INSERT INTO project (id, project_name, user_id) VALUES ('2', 'B', '42');
INSERT INTO project (id, project_name, user_id) VALUES ('4', 'D', '23');
INSERT INTO project (id, project_name, user_id) VALUES ('1', 'A', '19');
INSERT INTO project (id, project_name, user_id) VALUES ('3', 'C', '15');
INSERT INTO project (id, project_name, user_id) VALUES ('3', 'C', '29');
INSERT INTO project (id, project_name, user_id) VALUES ('2', 'B', '19');
INSERT INTO project (id, project_name, user_id) VALUES ('3', 'C', '19');



CREATE TABLE project_roles  
(
    id  INT,
    project_id  VARCHAR(512),
    user_id INT
);

INSERT INTO project_roles (id, project_id, user_id) VALUES ('1', '1', '23');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('2', '2', '42');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('3', '4', '23');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('4', '1', '19');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('5', '3', '15');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('6', '3', '29');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('7', '2', '19');
INSERT INTO project_roles (id, project_id, user_id) VALUES ('8', '3', '19');


CREATE TABLE users   
(
    id  INT,
    nickname VARCHAR(512)
);

INSERT INTO users (id, nickname) VALUES ('23', 'John');
INSERT INTO users (id, nickname) VALUES ('42', 'Doe');
INSERT INTO users (id, nickname) VALUES ('19', 'James');
INSERT INTO users (id, nickname) VALUES ('15', 'Mark');
INSERT INTO users (id, nickname) VALUES ('29', 'Peter');

---

Query #1

SELECT distinct U.id, U.nickname, PR.project_id, P.project_Name FROM users U
INNER JOIN project_roles PR ON PR.user_id = U.id
INNER JOIN project P ON P.id = PR.project_id
where  U.id = 19;

id	nickname	project_id	project_Name
19	James	1	A
19	James	2	B
19	James	3	C

---