加入多个表以在 MySQL 中获得 NOT EQUAL 值 [英] Joining multiple tables to get NOT EQUAL values in MySQL

问题描述

我已经尝试了 3 天来解决这个问题 - 现在我需要你们 :-)

I've been trying for 3 days to solve this problem - now I need you guys :-)

我需要加入 3 个表:

I need to join 3 tables:

table data        table user           table collection
data_id | colors  user_id | user_name  collection_id | user_id | data_id
----------------  -------------------  ---------------------------------
   1    | blue       1    | Brian            1       |    1    |    1
   2    | red        2    | Jason            2       |    2    |    3
   3    | green      3    | Marie            3       |    1    |    3
   4    | yellow                             4       |    3    |    2

输出是用户特定的.这意味着布赖恩不想看到蓝色和绿色，杰森不想看到绿色，玛丽不想看到红色.这应该给出以下输出:

The outputs are user specific. That means Brian don't want to see blue and green, Jason don't want to see green and Marie don't want to see red. That should give the following outputs:

for Brian           for Jason             for Marie
id | colors         id | colors           id | colors
-----------         -----------           -----------
2  | red            1  | blue             1  | blue
4  | yellow         2  | red              3  | green
                    4  | yellow           4  | yellow

这是我最好的尝试(目前)如何解决这个问题:

Here is my best attempt (for now) how to solve this:

SELECT 
    *
FROM
    data d
        INNER JOIN
    user u ON u.user_id = d.user_id
        LEFT JOIN
    collection c ON c.data_id = d.data_id
WHERE
    c.user_id <> '1' OR c.collection_id IS NULL

结果是，Brian 的选择将得到整理 - 这很好！但是因为Jason 也不想看到绿色，所以它仍然被Brian 选中.相当混乱！

The result is that Brian's selections will getting sorted out - that's good! But because Jason don't want to see green either it's still selected for Brian. Pretty confusing!

我希望我能或多或少地理解它=)

I hope i could write it more or less understandable =)

非常感谢所有帮助和提示！

All help and hints are really appreciated!

干杯

此解决方案扩展并缩短了此解决方案:在 MySQL 的 CROSS JOIN 中用另一个表中的用户名替换 id

this solution is extended and got shorter by this solution: replace the id with the username from an other table in a CROSS JOIN in MySQL

推荐答案

可能正在使用交叉联接而不是在 table_collection 中

Could be using a cross join and not in table_collection

select user.id, user.name, data.data_id, data.data
from user 
cross join data
where (  user.user_id, data.data_id) not in ( 
   select user_id, data_id 
   from table_collection
)  

这篇关于加入多个表以在 MySQL 中获得 NOT EQUAL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！