I have the following query:

SELECT u.id, u.name, n.other_user, n.notification FROM notifications n
INNER JOIN users u ON
CASE
  WHEN n.user = :me THEN u.id = n.other_user
  WHEN n.other_user = :me THEN u.id = n.user
  ELSE "CANCEL JOIN HERE"
END

Say for the query above I have notification table like this:

Now let's say I am logged in user 2. Therefore, I want to fetch records with id 1, 3, 4, 5. This is because records 1, 3 have user 2 in both user and other_user and records 4,5 are for everyone as they are not assigned to any specific user id (as they are 0,0). Also, when these records are selected they shouldn't be joined to users table as there are no users with id 0. Here, 0 means that notification was sent to each and every user on the site. It's for everyone. Therefore, I also want to cancel joining with users table when selecting these records with 0,0.

Now, my current query (without ELSE part) gives me only records 1,3 and ignores records 4,5. I believe this is because of the INNER JOIN used here. But I cannot use any other join. I need inner join. How can I select records 4,5 here as well?

CodePudding user response：

Use a separate query that gets the other records, and combine them with UNION

SELECT *
FROM (
    SELECT u.id, u.name, n.other_user, n.notification FROM notifications n
    INNER JOIN users u ON
    CASE
      WHEN n.user = :me THEN u.id = n.other_user
      WHEN n.other_user = :me THEN u.id = n.user
    END
    
    UNION
    
    SELECT n.user, '', n.other_user, n.notification
    FROM notification n
    WHERE n.user = 0 AND n.other_user = 0
) AS x
ORDER BY x.id