MySQL查询选择JOIN表中不存在ID的位置 [英] MySQL Query Select where id does not exist in the JOIN table
问题描述
我想知道一个用于解决此问题的MySQL查询:
I wanna know a single MySQL query for solving this problem:
我有一个包含成员ID和位置ID的表格.
I have a table that consists of member ids and location ids..
_________________________________________________________
| | | |
| ID | Member Id | Location |
|______|________________________|________________________|
| | | |
| 1 | 2371 | 7 |
| | | |
| 2 | 5123 | 10 |
| | | |
| 3 | 2371 | 9 |
| | | |
| 4 | 5123 | 9 |
| | | |
| 5 | 565 | 9 |
| | | |
| 6 | 2371 | 5 |
| | | |
| 7 | 5123 | 6 |
| | | |
|______|________________________|________________________|
另一个表由所有位置数据组成:
Another table consists of all the locations data:
__________________________________________________
| | |
| Location Id | Location Name |
|________________________|________________________|
| | |
| 1 | ABC |
| | |
| 2 | BCD |
| | |
| 3 | CDE |
| | |
| 4 | DEF |
| | |
| 5 | EFG |
| | |
| 6 | GHI |
| | |
| 7 | HIJ |
| | |
| 8 | IJK |
| | |
| 9 | JKL |
|________________________|________________________|
我想从不存在成员ID 2371的表中找到所有位置ID.如何在单个查询中找到它?我知道如果将查询分为两部分，就可以做到.就像所有位置的数组..和成员ID存在的所有位置的数组一样.然后使用PHP的！in_array函数
I wanna find all location ids from the table where member id 2371 is not present. How can I find this in single query? I know I can do it if I break the query into two pieces.. Like array of all locations.. and array of all locations where member id exist. Then using !in_array function of PHP
感谢您的时间和事先的帮助.
Thanks for your time and help in advance.
推荐答案
您可以通过执行以下操作找到成员2371所在的所有位置:
You can find all locations that member 2371 is in by doing:
SELECT location
FROM memberlocation
WHERE memberid = 2371
您可以通过以下方式找到所有其他位置(所需的位置)
You can find all other locations (the ones you want) by:
SELECT *
FROM location
WHERE locationid NOT IN
( SELECT location
FROM memberlocation
WHERE memberid = 2371)
这篇关于MySQL查询选择JOIN表中不存在ID的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
