SQL Query Select или Минус Помощь SQL Developer

Я работаю над простым запросом SQL в SQL Developer, и я не могу понять, что это правильно. Мой запрос: "Получить имена всех репетиторов, у которых были все встречи, с указанием" топоним здесь ""

Вот мой SQL:

SELECT Appt.TUTOR_USER_ID, TUTORS.FIRST_NAME, TUTORS.LAST_NAME
FROM Appt, 
  (
   SELECT Tutor.USER_ID, USR.FIRST_NAME, USR.LAST_NAME
   FROM Tutor, "USER" USR
   WHERE Tutor.USER_ID = USR.ID
  ) TUTORS,
  (
   SELECT USR.ID, USR.FIRST_NAME, USR.LAST_NAME
   FROM Student, "USER" USR
   WHERE Student.USER_ID = USR.ID
  ) USERS
WHERE (Appt.TUTOR_USER_ID = TUTORS.USER_ID AND Appt.STUDENT_USER_ID = USERS.ID)
  AND (USERS.FIRST_NAME = 'John' AND USERS.LAST_NAME = 'Smith');

Это дает мне всех наставников, у которых были встречи с Джоном Смитом, но не ВСЕ ИХ встречи с Джоном Смитом. Поэтому я получаю репетиторов, у которых были встречи с другими людьми, кроме Джона Смита. Может ли кто-нибудь помочь мне с правильными утверждениями или логикой здесь.

Источник

3 ответа

Решение

Вы ищете только те записи, которые соответствуют этому имени, поэтому в настоящее время больше нечего считать или исключать. Вы можете добавить and not exists пункт, который запрашивает любых других студентов; или так как это в названии вашего вопроса, minus это делает то же самое:

select a.tutor_user_id, tu.first_name, tu.last_name
from appt a
join tutor t on t.user_id = a.tutor_user_id
join student s on s.user_id = a.student_user_id
join "USER" tu on tu.id = t.user_id
join "USER" su on su.id = s.user_id
where su.first_name = 'John' and su.last_name = 'Smith'
minus
select a.tutor_user_id, tu.first_name, tu.last_name
from appt a
join tutor t on t.user_id = a.tutor_user_id
join student s on s.user_id = a.student_user_id
join "USER" tu on tu.id = t.user_id
join "USER" su on su.id = s.user_id
where not (su.first_name = 'John' and su.last_name = 'Smith');

Но я бы сделал это одним ударом, используя group by и having пункт для подсчета количества назначений для Джона Смита и для кого-либо еще:

select a.tutor_user_id, tu.first_name, tu.last_name
from appt a
join tutor t on t.user_id = a.tutor_user_id
join student s on s.user_id = a.student_user_id
join "USER" tu on tu.id = t.user_id
join "USER" su on su.id = s.user_id
group by a.tutor_user_id, tu.first_name, tu.last_name
having count(case when su.first_name = 'John' and su.last_name = 'Smith'
  then 1 else null end) > 0
and count(case when su.first_name = 'John' and su.last_name = 'Smith'
  then null else 1 end) = 0;

Демонстрация SQL Fiddle с некоторыми простыми данными, вашим исходным запросом (который также показывает дубликаты) и обеими этими версиями.

Как упоминали JosephB и Serpiton, и я забыл сказать, что вам на самом деле не нужно проходить через tutor или же student таблицы, так как они ничего не добавляют; Вы можете присоединиться к "USER" стол прямо из appt колонки:

select a.tutor_user_id, tu.first_name, tu.last_name
from appt a
join "USER" tu on tu.id = a.tutor_user_id
join "USER" su on su.id = a.student_user_id
group by a.tutor_user_id, tu.first_name, tu.last_name
having count(case when su.first_name = 'John' and su.last_name = 'Smith'
  then 1 else null end) > 0
and count(case when su.first_name = 'John' and su.last_name = 'Smith'
  then null else 1 end) = 0;

Как показано здесь.

В обоих случаях я переделал ваш оригинал, чтобы использовать ANSI join синтаксис, а не старый стиль Oracle, и удалил подзапросы, пока я был там, так как все объединения могут быть выполнены на одном уровне. Однако я настоятельно рекомендую переосмыслить имена таблиц, чтобы вам не приходилось использовать заключенный в кавычки идентификатор; может позвонить им users, tutors а также students вместо?

Источник

Другой способ - сначала отфильтровать встречи, чтобы те, где преподаватель видел только ученика, использовали его в качестве базы для основного запроса.

WITH tutor_appt As (
  SELECT tutor_user_id
       , MAX(student_user_id) student_user_id
  FROM   appt
  GROUP BY tutor_user_id
  HAVING COUNT(DISTINCT student_user_id) = 1
)
SELECT a.tutor_user_id, tu.first_name, tu.last_name
FROM   tutor_appt a
       INNER JOIN "USER" tu on tu.id = a.tutor_user_id
       INNER JOIN "USER" su on su.id = a.student_user_id
where  su.first_name = 'John' and su.last_name = 'Smith';

Если база данных нормальная, нет необходимости присоединяться к Tutor и Student стол к appt до присоединения USER: tutor_user_id а также student_user_id столбцы могут быть присоединены к USER непосредственно.

Источник

Вот альтернатива, которая обходит таблицы TUTORS и STUDENTS, потому что оба Appt.TUTOR_USER_ID а также Appt.STUDENT_USER_ID должен существовать как "USER".ID,

SELECT
  DISTINCT
  Appt.TUTOR_USER_ID, Tutors.FIRST_NAME, Tutors.LAST_NAME
FROM Appt
INNER JOIN
    (SELECT
     Appt.TUTOR_USER_ID, Users.FIRST_NAME, Users.LAST_NAME
    FROM Appt
    INNER JOIN "USER" Users
    ON Appt.TUTOR_USER_ID = Users.ID) Tutors
ON Appt.TUTOR_USER_ID = Tutors.TUTOR_USER_ID
INNER JOIN "USER" Users
ON Appt.STUDENT_USER_ID = Users.ID 
WHERE Users.FIRST_NAME = 'John' AND Users.LAST_NAME = 'Smith';

Имена преподавателей получены путем объединения с таблицей USER. Затем таблица Appt снова соединяется с таблицей USER для фильтрации имени данного учащегося.

Пожалуйста, ознакомьтесь с демонстрацией SQL Fiddle.

Источник
Другие вопросы по тегам