mySQL 选择帮助.如果或存在? [英] mySQL SELECT help. IF or EXISTS?

问题描述

我有这个数据库结构

* user
user_id
name

* client
client_id
name

* user_client
user_client_id
user_id
client_id

* message
message_id
client_id
description

如果 user_client 上有条目，则用户的权限仅限于表中为其 ID 列出的特定客户端.如果没有条目，则用户可以访问任何客户端.

If there are entries on user_client then the user has permissions restricted to the specific clients listed for his id on the table. If there are no entries, then the user has access to any client.

如何只选择用户可以阅读的消息?

How can I select only messages that the user can read?

我正在尝试对 WHERE 子句执行 IF 以检查 user_client 表中是否有任何条目，但我不知道从那里开始.如果user_client 上没有条目或只有user_client 表

I'm trying to do an IF on the WHERE clause to check if any entries on the user_client table but I don't know where to go from there. It needs to select all messages from any client if no entries on user_client or only messages for client_id specified on user_client table

感谢您的帮助！

推荐答案

我建议做两个不同的查询:一个针对超级用户，另一个针对受限用户.然后就可以用 UNION 将两个结果连接起来.

I would suggest doing two different queries: one for the superusers and the other for the restricted users. Then you can join the two results with a UNION.

    SELECT M.message_id,
           M.client_id,
           M.description
      FROM message M
INNER JOIN user_client UC ON (UC.client_id = M.client_id)
INNER JOIN user U ON (UC.user_id = U.id)
     WHERE U.id = :user_id

     UNION

    SELECT M.message_id,
           M.client_id,
           M.description
      FROM message M
     WHERE NOT EXISTS ( 
               SELECT *
                 FROM user_client
                WHERE user_id = :user_id
           )

您可以通过其他查询获得相同的结果，但恕我直言，这个查询更清晰，更易于维护.

You can obtain the same result with other queries but IMHO this one is clearer and more maintainable.

如果您想确保用户存在，您应该将第二个查询与用户表连接起来.

If you want to ensure that the user exists you should join the second query with the user table.

    SELECT M.message_id,
           M.client_id,
           M.description
      FROM message M
      JOIN user U
     WHERE U.id = :user_id
       AND NOT EXISTS ( 
               SELECT *
                 FROM user_client
                WHERE user_id = :user_id
           )

这篇关于mySQL 选择帮助.如果或存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！