Question 88829 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql select where condition in another table

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

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you everyone for helping me.

    SELECT DISTINCT project.id, project.user_id, project.project_name
FROM project
LEFT JOIN project_roles ON project.id = project_roles.project_id
WHERE project.user_id = 1 OR project_roles.user_id = 1;

    This is answer which works for me.


  2. 0

    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
    Login or Signup to reply.
  3. 0 
    create table USERS (id, nickname) as select 1, 'test';

create table PROJECTS (id, user_id, project_name) as
select 7777, 2, 'NAME1'
union
select 8888, 1, 'NAME2'
union
select 9999, 1, 'NAME3';

create table PROJECT_ROLES (id, user_id, project_id) as
select 5, 1, 7777;

    You should use set operator union for that :

    select id, project_name
  from projects
 where user_id = 1
union
select p.id, p.project_name
  from project_roles r, projects p
 where r.user_id = 1
   and p.id = r.project_id;

  id  | project_name 
------+--------------
 7777 | NAME1
 9999 | NAME3
 8888 | NAME2

    In case a user is in PROJECTS table and PROJECT_ROLES table, the set operator UNION will remove duplicates.

    Login or Signup to reply.
  4. 0

    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

    First query (above UNION) returns projects that are in the project table. This is simple.

    Second query returns projects in project_roles which are not in project for that user. Also returns the project name. This assumes the projects table is not empty and has at least one record for each project present in project_roles.

    SELECT id, 
       user_id, 
       project_name, 
       'In projects table' AS msg
FROM projects
WHERE user_id = 1
UNION 
SELECT DISTINCT 
       pr.project_id, 
       pr.user_id, 
       COALESCE(pn.project_name,'Project name not available'), 
       'Not in projects table'
FROM project_roles pr
  LEFT JOIN (SELECT DISTINCT project_id, project_name FROM projects) pn 
    ON pr.project_id = pn.project_id
WHERE pr.user_id = 1
AND NOT EXISTS (SELECT 1 
                FROM projects p
                WHERE pr.user_id = p.user_id
                AND pr.project_id = p.id)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search