0

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:

id user other_user notification
1 5 2 Text 1
2 3 3 Text 2
3 2 16 Text 3
4 0 0 Text 4
5 0 0 Text 5

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?

Relaxing Music
  • 452
  • 4
  • 13

1 Answers1

1

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
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • How do I combine `ORDER BY` both of them to give descending ordering for both queries in a structured manner? – Relaxing Music Sep 23 '21 at 08:50
  • What about the blank `''` in between `n.user` and `n.other_user` ? – Relaxing Music Sep 23 '21 at 08:56
  • All the queries in a UNION have to return the same number of columns. That's a placeholder for the nonexistent names for these. – Barmar Sep 23 '21 at 08:56
  • See https://stackoverflow.com/questions/43742028/apply-order-by-on-a-union-mysql for how to order the result of a UNION. – Barmar Sep 23 '21 at 08:57
  • Oh so you mean to say if in first query I have 10 columns so I need to have 10 in second too? Also, the order in which I need to place the blank quotes according to the first query, is that important? – Relaxing Music Sep 23 '21 at 08:57
  • Of course. UNION simply concatenates the results of the two queries. If you don't have them in the same order, you'll get confusing results because the column name is based on the first query. – Barmar Sep 23 '21 at 09:02
  • I am getting this error `Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1250 Table 'n' from one of the SELECTs cannot be used in ORDER clause in` – Relaxing Music Sep 23 '21 at 09:02
  • You can't use aliases from the subquery in the main query. – Barmar Sep 23 '21 at 09:04
  • Thank you. Now all is good. Learned a lot of things today. Can you explain me one last thing? In your final query `ORDER BY x.id` gives me the required descending order but when I used `ORDER BY x.id DESC` it gave me the ascending order. Why `DESC` is not used here OR why does it give the vice versa results? – Relaxing Music Sep 23 '21 at 09:12
  • I can't think of any reason why it wouldn't order correctly. What is the datatype of `id`? – Barmar Sep 23 '21 at 09:13
  • `id` in the notification table? It's `bigint` – Relaxing Music Sep 23 '21 at 09:17
  • If `users.id` and `notifications.user` are both integers, it should order numerically. But if either of them is a string, the common type will be a string, and it will order lexicographically. – Barmar Sep 23 '21 at 14:56
  • Lexicographically. That's a new word in my life. But all the tables joined in this have their `id` as `BIGINT` only. – Relaxing Music Sep 23 '21 at 20:24
  • But we're not using `notifications.id`, we're using `notifications.user` in the second subquery. Is that also BIGINT? – Barmar Sep 23 '21 at 20:26
  • Yes. That too is `BIGINT`. I kept that because I wanted to be future ready to avoid any collisions from large user base (if I ever achieve that). – Relaxing Music Sep 23 '21 at 22:56
  • Sometimes MySQL doesn't infer the type of the combine column correctly. – Barmar Sep 23 '21 at 22:57
  • Hi.. I just noticed a new issue here. The records are not getting sorted in order properly. This issue was there that time too but I had missed noticing it. What should happen is that the records should be sorted like this as per id `5,4,3,1` which means both queries should be returning combined sorting. But it's returning individual sorting for both queries separately. Like it returns `4,5,3,1`. Records `4,5` (ascending order) is coming from 2nd query which should be `5,4` (descending order) and `3,1` (first query gets sorted properly) at the last from 1st query. Can you please check? – Relaxing Music Sep 27 '21 at 01:44
  • I have raised a question for this here https://stackoverflow.com/questions/69340399/combined-order-by-of-two-select-queries-using-union – Relaxing Music Sep 27 '21 at 01:59
  • The ordering happens after everything is combined by the `UNION`. – Barmar Sep 27 '21 at 02:09