I am not able to get the result when joining two tables with where clause with OR condition

Posted on

Question :

I have two tables projects and project_user_cross. I want to get the projects if the user id exists in either of the two tables

Here is the projects table

Id | project_manager_id | name | description | created_at | updated_at | progress
1  | 1                  | p 1  | lorem ipsum | 2019-9-16  |            | 0

Here is the project_user_cross table (empty)

Id | project_id  | user_id

Here is the query I tried:

SELECT * 
FROM projects p 
INNER JOIN project_user_cross c ON c.project_id = p.id 
WHERE p.project_manager_id =1 OR c.user_id =1

Since project_manager_id = 1 exists I want the query to return the projects but its returning nothing.

Answer :

except the Id is returning null

You must obtain 2 columns named ID. First – from projects table with value of 1, second – from project_user_cross table with value of NULL.

To distinguish them you must replace the asterisk with table.name fields list and unique aliases when fieldnames are equal (one field may stay unnamed).

SELECT p.Id, -- do not rename
       p.project_manager_id, 
       p.name, 
       p.description, 
       p.created_at, 
       p.updated_at, 
       p.progress, 
       c.Id AS crosstab_id, -- rename 
       c.project_id, 
       c.user_id
FROM projects p 
LEFT JOIN project_user_cross c 
   ON c.project_id=p.id 
WHERE p.project_manager_id=1 
   OR (    c.user_id=1 
        OR c.user_id IS NULL )

The join happens – logically – before the WHERE clause is applied. You can use an EXISTS, moving the join to a subquery:

SELECT p.* 
FROM projects  p
WHERE p.project_manager_id = 1
   OR EXISTS
      ( SELECT 1
        FROM project_user_cross  c
        WHERE c.project_id = p.id 
          AND c.user_id = 1
      ) ;

Leave a Reply

Your email address will not be published. Required fields are marked *