Блог

У меня есть запрос, для создания которого я, похоже, не могу разобраться. У меня есть три таблицы:

student : сведения об ученике

link : ссылки, которые существуют для студента, ссылки имеют статус, который может быть активным или завершенным

email : показывает, какие ссылки были отправлены по электронной почте.

Мне нужно получить список идентификаторов учащихся (из таблицы student) на основе следующих критериев:

1. ссылка.status = активна, а связанное с ней электронное письмо не существует (т. Е. ссылка была создана, но она не была отправлена по электронной почте)
2. ссылка.status равна нулю, а email равен нулю (т. Е. Для этого студента нет существующих ссылок)
3. link.status = завершено, и для этого студента нет других ссылок, которые имеют активный статус

Итак, если у меня есть следующие данные в моих таблицах:

student

  ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
|  4 |
 ---- 
  

link

  ---- ----------- ------------ 
| id |  status   | student_id |
 ---- ----------- ------------ 
|  1 | completed |          1 |
|  2 | active    |          1 |
|  3 | completed |          2 |
|  4 | active    |          3 |
 ---- ----------- ------------ 
  

email

  ---- --------- 
| id | link_id |
 ---- --------- 
|  1 |       1 |
|  2 |       2 |
|  3 |       3 |
 ---- --------- 
  

Тогда мой запрос должен вернуть следующие идентификаторы учащихся: 2,3,4

2 — потому что для этого студента есть только заполненная ссылка

3 — потому что есть активная ссылка без соответствующего электронного письма

4 — потому что нет ссылок для этого студента

В настоящее время у меня есть этот запрос, который получает часть того, что мне нужно:

 SELECT DISTINCT student.id
FROM student
LEFT JOIN link ON link.student_id = student.id
LEFT JOIN email ON email.link_id = link.id
WHERE student.course =  'phd'
AND student.institution_id =  '2'
AND (
  (link.status !=  "active" AND email.id IS NULL)
OR 
  (link.status IS NULL AND email.id IS NULL)
OR 
  (link.status =  "active" AND email.id IS NULL)
)
  

Это, конечно, не дает никаких идентификаторов студента, где link.status = завершено, и никаких других ссылок для студента не существует. Я, конечно, могу сделать это, добавив в:

 (link.status = "completed" and email.id IS NOT NULL)
  

в WHERE , но это вернет идентификатор студента, если у них есть другая активная ссылка или у них нет активной ссылки. Это тот бит, с которым я борюсь.

У меня такое чувство, что это может быть невозможно выполнить одним запросом, так что мне нужно будет выполнить два запроса, а затем вычесть их друг из друга? Т.е. приведенный выше запрос и отдельный запрос, выбирающий ссылки со статусом «завершено», а затем вычитающий их из первого запроса?

Мое приложение, использующее эти запросы, построено на PHP, поэтому я рад выполнить некоторую логику на PHP с двумя запросами, если это необходимо.

(Понятия не имел, что вставить в заголовок, поэтому, если кто-нибудь может придумать что-нибудь получше, пожалуйста, отредактируйте это!)

 SELECT s.* 
  FROM student s 
  LEFT 
  JOIN link l 
    ON l.student_id = s.id 
   AND l.status <> 'completed' 
  LEFT 
  JOIN email e 
    ON e.link_id = l.id 
 WHERE e.id IS NULL;
  

?

Поскольку ваш запрос основан на ссылках, а у одного учащегося может быть более одной ссылки, вам следует начать с запроса к таблице ссылок, после чего вы добавляете объединения и условия.

Подготовка SQL:

 CREATE TABLE IF NOT EXISTS student 
  (
    id int auto_increment primary key, 
    course tinytext, 
    institution_id int
  );
INSERT INTO student (id, course, institution_id) VALUES
  (1, 'phd', 2),
  (2, 'phd', 2),
  (3, 'phd', 2),
  (4, 'phd', 2);
CREATE TABLE IF NOT EXISTS link 
  (
    id int auto_increment primary key, 
    status tinytext, 
    student_id int
  );
INSERT INTO link (id, status, student_id) VALUES
  (1, 'completed', 1),
  (2, 'active', 1),
  (3, 'completed', 2),
  (4, 'active', 3);
CREATE TABLE IF NOT EXISTS email 
  (
    id int auto_increment primary key, 
    link_id int
  );
INSERT INTO email (id, link_id) VALUES
  (1, 1),
  (2, 2),
  (3, 3);
  

Запрос:

 SELECT DISTINCT s.id
FROM link l
LEFT JOIN student s ON l.student_id = s.id
LEFT JOIN email e ON l.id = e.link_id
WHERE s.course =  'phd'
AND s.institution_id =  '2'
AND (
  (l.status !=  "active" AND e.id IS NULL)
  OR 
  (l.status IS NULL AND e.id IS NULL)
  OR 
  (l.status =  "active" AND e.id IS NULL)
)
  

Поиграйте с этим:http://sqlfiddle.com /#!2/dae16b/2

Я не совсем понимаю ваш вопрос, потому что в нем много ошибок. Я попытаюсь копнуть глубже, чтобы выяснить, в порядке ли ваша логика.

РЕДАКТИРОВАТЬ: «Клубничный» подход путем фильтрации СОЕДИНЕНИЙ может быть тем, что вам нужно

 SELECT s.id 
  FROM student s 
  LEFT JOIN link l 
    ON l.student_id = s.id AND l.status = 'active' OR l.status IS NULL 
  LEFT JOIN email e 
    ON e.link_id = l.id 
WHERE 
  e.id IS NULL 
  AND s.course =  'phd' 
  AND s.institution_id =  '2';
  

Поиграйте с этим:http://sqlfiddle.com /#!2/dae16b/26

Мы выбираем таблицу «student» и добавляем только те ссылки, которые имеют статус «active» или «null» ( LEFT JOIN link l ON l.student_id = s.id AND l.status != 'completed' ), что соответствует правилу № 2 (для этого студента нет существующих ссылок) и первой части правила № 1 (ссылка была создана, но она не была отправлена по электронной почте) и второй части правила №3 (link.status = завершено, и для этого студента нет других ссылок с активным статусом).). После этого, чтобы решить для второго параметра правила # 2 (ссылка была создана, но она не была отправлена по электронной почте), мы удаляем строки, у которых нет электронной почты ( JOIN email e ON e.link_id = l.id и e.id IS NULL часть).

Осталось только подумать, нужно ли вам решить для первой части правила № 3 (link.status = завершено, и для этого студента нет других ссылок, которые имеют активный статус), потому что я не знаю, есть ли ситуация, когда «у студента нет ссылок» = «у студента есть статус link.completed».

На данный момент этот запрос возвращает то, что вы просили.