Как <выбрать> только ни одного существующего пользователя из кейса. ВЫБОР SQL
Работаем над выбором всех пользователей в html <select>, которого в нем еще нет.
Я работаю над веб-сайтом «дел», где я могу размещать пользователей на делах. Пользователей можно поставить на несколько дел одновременно. Я делаю html <select> со всеми пользователями, которых я могу добавить в дело, и важно, чтобы уже существующие пользователи в этом деле не отображались.
У меня есть 3 таблицы SQL. Users, Cases и Junc_jobs. Junc_Jobs работает как отношение «многие ко многим» для связи пользователей и обращений. Все пользователи, которых я хочу выбрать, это fk_role > 1.
USERS
+-------+------------+----------+
| id | firstName | fk_case |
+-------+------------+----------+
| 05 | Rasmus | 57 |
| 06 | Ulrik | 57 |
| 10 | Nick | 59 |
| 12 | Lisa | 59 |
| 22 | Marco | 65 |
| 23 | Frederik | 63 |
+-------+------------+----------+
CASES
+-------+------------+
| id | caseName |
+-------+------------+
| 57 | case 1 |
| 59 | case 2 |
| 63 | case 3 |
| 64 | case 4 |
| 65 | case 5 |
+-------+------------+
JUNC_JOBS
+-------+------------+--------------+
| id | fk_case | fk_employee |
+-------+------------+--------------+
| 1 | 57 | 5 |
| 2 | 59 | 5 |
| 3 | 64 | 23 |
| 4 | 65 | 23 |
| 5 | 65 | 22 |
| 6 | 66 | 10 |
| 7 | 61 | 22 |
| 8 | 60 | 5 |
| 9 | 63 | 5 |
| 10 | 66 | 22 |
| 11 | 62 | 12 |
+-------+------------+--------------+
До сих пор я пытался сделать что-то вроде этого:
SELECT users.id, users.firstName, users.lastName, junc_jobs.fk_case FROM users
LEFT JOIN junc_jobs ON users.id = junc_jobs.fk_employee
WHERE (fk_case IS NULL AND users.fk_role > 1) OR (fk_case != 66 AND users.fk_role > 1)
ORDER BY firstName
НО, если junc_jobs содержит пользователей с несколькими случаями, он перестает работать. Получаем что-то вроде этого:
+-------+------------+----------+
| id | firstName | fk_case |
+-------+------------+----------+
| 06 | Ulrik | 57 |
| 05 | Rasmus | 57 |
| 12 | Lisa | 59 |
| 10 | Nick | 59 |
| 05 | Rasmus | 59 |
| 23 | Frederik | 63 |
| 06 | Macro | 63 |
| 23 | Frederik | 64 |
| 23 | Frederik | 65 |
| 22 | Marco | 65 |
| 10 | Nick | 65 |
+-------+------------+----------+
Как видите, пользователи, которые находятся в fk_case = 66, удаляются, но если этот пользователь уже находится в других делах, они все равно отображаются. Пользователь с id = 22 включен в fk_case 66, и этот пользователь полностью удален из списка.
Я попытался использовать GROUP BY ID, чтобы показать только одного из каждого пользователя, но также отображаются пользователи, которые уже находятся в деле и имеют другие дела.
Вы включаете текущий вывод, но каким будет ваш желательный вывод? Потому что, если вы ищете пользователей для случая 66, то fk_case не должен иметь значения для комбинации SELECT.
Почему в таблице users есть столбец fk_case? Разве не для этого предназначена таблица junc_jobs? А где столбец fk_role в users?
@Barmar Может быть, это текущий или первый случай? Всё равно там быть не должно
Ответы 2
Я не думаю, что вам нужен fk_case для SELECT, потому что если пользователь находится в других проектах, это не имеет значения. Только дело в том, что он не на текущем.
SELECT DISTINCT users.id, users.firstName, users.lastName
FROM users
LEFT JOIN junc_jobs ON users.id = junc_jobs.fk_employee
WHERE users.fk_role > 1
AND (fk_case IS NULL OR fk_case != 66)
ORDER BY firstName
Также не уверен, что делает junc_jobs, но, возможно, что-то вроде этого может помочь
SELECT u.*
FROM users u
WHERE u.fk_role > 1
AND NOT EXISTS ( SELECT 1
FROM junc_jobs j
WHERE j.fk_employee = u.id and j.fk_case = 66)
Поместите тест fk_case в предложение ON, а затем покажите только тех пользователей, которые не совпадают.
SELECT DISTINCT u.id, u.firstName, u.lastName
FROM users as U
LEFT JOIN junc_jobs AS j ON u.id = j.fk_employee AND j.fk_case = 66
WHERE j.fk_case IS NULL AND u.fk_role > 1
Кажется, это работает. Я просто добавил fk_role > в WHERE. Я не знал, что можно добавить И к ЛЕВОМУ СОЕДИНЕНИЮ. Большое спасибо за помощь ^^
Ваше условие WHERE можно упростить до
(fk_case IS NULL OR fk_case != 66) AND users.fk_role > 1