MySQL – How to Filter Not NULL Rows After Multiple LEFT JOIN?

MySQL

I want to JOIN a base table with another 23 tables. But as I want to join one with another tables, I have to use LEFT JOIN. My query sample:

SELECT notif_dest_user.notif_id,
       notif_all.da,
       COALESCE(notif_trick_like.uft_id, notif_trick_comment.uft_id) AS uft_id,
       COALESCE(like_users.un, trickComment_users.un)                AS un,
       COALESCE(like_tricks.tr_n, trickComment_tricks.tr_n)          AS tr_n,
       trickComment_userForTricks.cm_tx,
       COALESCE(trickComment_users.av_ul,like_users.av_ul)                       AS av_ul
FROM notif_dest_user
       LEFT JOIN notif_all ON notif_all.notif_id = notif_dest_user.notif_id
       LEFT JOIN notif_trick_like ON notif_dest_user.notif_id = notif_trick_like.notif_id
       LEFT JOIN user_for_tricks like_userForTricks ON notif_trick_like.uft_id = like_userForTricks.uft_id
       LEFT JOIN tricks like_tricks ON like_userForTricks.tr_id = like_tricks.tr_id
       LEFT JOIN users like_users ON like_userForTricks.u_id = like_users.u_id
       LEFT JOIN notif_trick_comment ON notif_trick_comment.notif_id = notif_dest_user.notif_id
       LEFT JOIN user_for_tricks trickComment_userForTricks
         on trickComment_userForTricks.uft_id = notif_trick_comment.uft_id
       LEFT JOIN users trickComment_users ON trickComment_users.u_id = trickComment_userForTricks.u_id
       LEFT JOIN tricks trickComment_tricks ON trickComment_tricks.tr_id = trickComment_userForTricks.tr_id
where notif_dest_user.dest_u_id = 42
limit 10;

The result of that query is like this:

+----------+------------+---------+-------------+-------------+-------------+------------------------------------+
| notif_id | da         | uft_id  | un          | tr_n        | cm_tx       | av_ul                              |
+----------+------------+---------+-------------+-------------+-------------+------------------------------------+
|       10 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
|       11 | 1542866579 | 1799494 | Some result | Some result | Some result | pics/avatars/u2764_1504729707_3092 |
|       12 | 1542866579 | 1799386 | Some result | Some result | Some result |                                    |
|       13 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
|       14 | 1542866579 | 1799494 | Some result | Some result | Some result | pics/avatars/u2764_1504729707_3092 |
|       15 | 1542866579 | 1799386 | Some result | Some result | Some result |                                    |
|       16 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
|       17 | 1542866579 | 1799494 | Some result | Some result | Some result | pics/avatars/u2764_1504729707_3092 |
|       18 | 1542866579 | 1799386 | Some result | Some result | Some result |                                    |
|       19 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
+----------+------------+---------+-------------+-------------+-------------+------------------------------------+

But I don't want to get the rows that were not exist (NULL) in right-side tables (other 23 tables). In other words, I want to show sum of rows of separately INNER JOIN between the base table and other tables. Something like this:

+----------+------------+---------+-------------+-------------+-------------+-------------------------------------+
| notif_id | da         | uft_id  | un          | tr_n        | cm_tx       | av_ul                               |
+----------+------------+---------+-------------+-------------+-------------+-------------------------------------+
|    10000 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10001 | 1542866579 | 1799554 | some result | some result | some result | pics/avatars/u87332_1521754761_3673 |
|    10002 | 1542866579 | 1799422 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10003 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10004 | 1542866579 | 1799554 | some result | some result | some result | pics/avatars/u87332_1521754761_3673 |
|    10005 | 1542866579 | 1799422 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10006 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10007 | 1542866579 | 1799554 | some result | some result | some result | pics/avatars/u87332_1521754761_3673 |
|    10008 | 1542866579 | 1799422 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10009 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
+----------+------------+---------+-------------+-------------+-------------+-------------------------------------+

Best Answer

Two options:

1) If you want to filter out rows that have no match in the right table, skip the LEFT keyword and use the regular (inner) join.

2) If you want to filter out rows that have all expressions evaluated to NULL, use regular WHERE clause, like:

SELECT ...
FROM ...
WHERE COALESCE(notif_trick_like.uft_id, notif_trick_comment.uft_id) IS NOT NULL
OR COALESCE(like_users.un, trickComment_users.un) IS NOT NULL
OR COALESCE(like_tricks.tr_n, trickComment_tricks.tr_n) IS NOT NULL
OR trickComment_userForTricks.cm_tx IS NOT NULL
OR COALESCE(trickComment_users.av_ul,like_users.av_ul) IS NOT NULL
Related Question