mysql - SQL高级SELECT语句

translations
+---------+----------------+----------+---------+
| id_user | id_translation | referrer | id_word |
+---------+----------------+----------+---------+
|       1 |              3 |     NULL |       4 |
|       1 |             17 |     NULL |       3 |
|       2 |             17 |     NULL |       5 |
|       2 |             17 |     NULL |       1 |
|       2 |             17 |     NULL |       7 |

words
+----+------+
| id | word |
+----+------+
|  4 | out  |
+----+------+

users_translations
+---------+----------------+----------+---------+
| id_user | id_translation | referrer | id_word |
+---------+----------------+----------+---------+
|       1 |             17 |     1    |       4 |
|       2 |             17 |     2    |       4 |
|       3 |             18 |     NULL |       4 |

我需要为currenttranslations和word选择allid_translation，但如果在行referrer = 1（currentuser）中，则不需要其他结果（currenttranslations的其他用户的word），如果没有referrer = 1，则显示all。

SELECT DISTINCT `t`.*, `ut`.`id_user` AS tuser
FROM translations AS t
LEFT JOIN users_translations AS ut ON `t`.`id` = `ut`.`id_translation`
INNER JOIN words ON `words`.`id` = `ut`.`id_word` OR `words`.`id` = `t`.`id_word`
WHERE (`word` = 'help')
ORDER BY `t`.`translation` ASC


+----+-------------+---------+---------+-------+
| id | translation | id_word | id_user | tuser |
+----+-------------+---------+---------+-------+
| 17 | допомагати  |       4 |       1 |     2 |
| 17 | допомагати  |       4 |       1 |     1 |

第一行不需要，因为我们有tuser = 1。如果没有tuser = 1，则应返回所有结果。
我不知道如何构建select语句，我将非常感谢有人告诉我如何使它工作。

最佳答案

首先想到的是

--add this to your where clause
    id_user <=
        CASE WHEN EXISTS(SELECT * FROM translations WHERE id_user = 1 AND id_word = words.id_word)
        THEN 1
        ELSE (SELECT MAX(Id) FROM translations)
        END