SQL-соединение по отношению «один ко многим», где ни одно из многих не соответствует заданному значению

Скажем, у меня есть две таблицы

User
-----
id
first_name
last_name

User_Prefs
-----
user_id
pref

Примеры данных в User_Prefs могут быть

user_id | pref
  2     |  SMS_NOTIFICATION
  2     |  EMAIL_OPT_OUT
  2     |  PINK_BACKGROUND_ON_FRIDAYS

А у некоторых пользователей могут отсутствовать соответствующие строки в User_Prefs.

Мне нужно запросить имя и фамилию любого пользователя, который НЕ имеет EMAIL_OPT_OUT в качестве одной из своих (возможно, многих, возможно, ни одной) строк User_Pref.

SELECT DISTINCT u.* from User u
LEFT JOIN User_Prefs up ON (u.id=up.user_id)
WHERE up.pref<>'EMAIL_OPT_OUT'

дает мне всех, у кого есть хотя бы одна строка, отличная от «EMAIL_OPT_OUT», что, конечно, не то, что мне нужно. Мне нужны все, у кого нет строк, соответствующих «EMAIL_OPT_OUT».

Есть ли способ, чтобы тип соединения и условия соединения отфильтровывали строки, которые я хочу оставить здесь? Или мне нужен подзапрос?


sql join one-to-many
person Jacob Mattison    schedule 20.07.2010    source источник
comment
это может помочь explainextended.com/2009/09/15/   -  person Francisco    schedule 21.07.2010

Ответы (2)


arrow_upward
12
arrow_downward

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

select distinct u.* from User u
left join User_Prefs up ON u.id = up.user_id and up.pref = 'EMAIL_OPT_OUT'
where up.user_id is null
person Mayo    schedule 20.07.2010
comment
+1 Здесь важно отметить, что вы можете присоединиться по любым критериям и комбинировать критерии. Вы не ограничены присоединением к совпадающим ключам. - person Marcus Adams; 21.07.2010
comment
+1 - мне это нравится больше, чем мой ответ, и почти наверняка быстрее. - person Eric Petroelje; 21.07.2010
comment
Абсолютно то, что я искал. Я тоже был так близок к этому — просто у меня не хватило мозгов присоединиться к равным, а не к неравным. - person Jacob Mattison; 21.07.2010
comment
Объяснение того, почему оригинал не работал, а это работает, см.: wiki.lessthandot.com /index.php/WHERE_conditions_on_a_LEFT_JOIN - person HLGEM; 21.07.2010

arrow_upward
0
arrow_downward

Почему бы не сохранить ваши пользовательские настройки в пользовательской таблице в виде логических полей? Это значительно упростит ваши запросы.

SELECT * FROM User WHERE EMAIL_OPT_OUT = false
person Marcus Adams    schedule 20.07.2010
comment
Разве я не хотел бы изменить схему. Это не моя база данных; На самом деле я присоединяюсь к представлению, исходящему из проприетарной БД. Хотя я хотел бы отметить, что у вашего подхода есть недостаток, заключающийся в том, что набор возможных параметров должен быть жестко закодирован в схеме базы данных. - person Jacob Mattison; 21.07.2010
comment
Это также значительно усложнило бы обслуживание, когда вам нужно добавить новый тип. Связанная таблица, как на плакате, имеет правильный дизайн. - person HLGEM; 21.07.2010
comment
@HLGEM, я не согласен с проблемой обслуживания, но я использую MVC, который абстрагирует все это. - person Marcus Adams; 21.07.2010