从MySQL中的连接中获取仅在一个表中的元素

I have two tables, network_permissions and device_permissions like this:

我有两个表,network_permissions和device_permissions如下:

NETWORK_PERMISSIONS
user_id network_id  perm
1        1           3
1        2           4
2        3           3
2        2           1
3        2           1
4        2           3


DEVICES_PERMISSIONS
user_id network_id  device_id   perm
1        2           2           1
2        2           2           1

Now, I would take only user_id that for the net=2 are in the network_permissions table (within users with perm=3), but not in the device_permissions.

现在,我只接受net_2的user_id在network_permissions表中(在perm = 3的用户中),但不在device_permissions中。

I would in this case the result is: 3 because is the user that have perm!=3 in the network_permission table for the net=2, but is not present in the device_permissions table.

在这种情况下,我的结果是:3因为net_ = 2的network_permission表中有perm!= 3的用户,但是在device_permissions表中不存在。

Sorry if it's confused... Thank you for the help.

对不起,如果它很困惑...谢谢你的帮助。

2 个解决方案

#1

SELECT n.user_id FROM network_permissions AS n Left JOIN devices_permissions AS d ON n.user_id = d.user_id WHERE d.user_id is null and n.network_id=2 and n.perm=3

SELECT n.user_id FROM network_permissions AS n左JOIN devices_permissions AS d ON n.user_id = d.user_id WHERE d.user_id为null且n.network_id = 2且n.perm = 3

#2

Select NETWORK_PERMISSIONS.network_id FROM NETWORK_PERMISSIONS LEFT JOIN DEVICES_PERMISSIONS on NETWORK_PERMISSIONS.network_id = DEVICES_PERMISSIONS.network_id AND NETWORK_PERMISSIONS.user_id = DEVICES_PERMISSIONS.user_id