SQL bergabung pada relasi satu-ke-banyak di mana tidak ada satupun yang cocok dengan nilai tertentu

Katakanlah saya punya dua meja

User
-----
id
first_name
last_name

User_Prefs
-----
user_id
pref

Contoh data di User_Prefs mungkin

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

Dan beberapa pengguna mungkin tidak memiliki baris yang sesuai di User_Prefs.

Saya perlu menanyakan nama depan dan nama belakang pengguna mana pun yang TIDAK memiliki EMAIL_OPT_OUT sebagai salah satu baris User_Pref (mungkin banyak, mungkin tidak ada).

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

memberi saya semua orang yang memiliki setidaknya satu baris yang bukan "EMAIL_OPT_OUT", yang tentu saja bukan yang saya inginkan. Saya ingin semua orang tidak memiliki baris yang cocok dengan "EMAIL_OPT_OUT".

Apakah ada cara agar tipe gabungan dan kondisi gabungan memfilter baris yang ingin saya tinggalkan di sini? Atau apakah saya memerlukan sub-kueri?


sql join one-to-many
person Jacob Mattison    schedule 20.07.2010    source sumber
comment
ini mungkin membantu explainexended.com/2009/09/15/   -  person Francisco    schedule 21.07.2010

Jawaban (2)


arrow_upward
12
arrow_downward

Saya pribadi berpikir jenis klausa "di mana tidak ada" mungkin lebih mudah dibaca, tapi inilah kueri dengan gabungan yang melakukan hal yang sama.

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 Hal penting yang perlu diperhatikan di sini adalah Anda dapat bergabung pada kriteria apa pun, dan menggabungkan kriteria. Anda tidak dibatasi untuk bergabung pada kunci yang cocok. - person Marcus Adams; 21.07.2010
comment
+1 - Saya lebih menyukai ini daripada jawaban saya, hampir pasti lebih cepat juga. - person Eric Petroelje; 21.07.2010
comment
Benar-benar apa yang saya cari. Saya juga sangat dekat - hanya saja saya tidak mempunyai pemikiran untuk bergabung dalam hal yang setara dan bukan yang tidak setara. - person Jacob Mattison; 21.07.2010
comment
Untuk penjelasan mengapa yang asli tidak berfungsi dan yang ini berfungsi, lihat: wiki.lessthandot.com /index.php/WHERE_conditions_on_a_LEFT_JOIN - person HLGEM; 21.07.2010

arrow_upward
0
arrow_downward

Mengapa preferensi pengguna Anda tidak disimpan di tabel pengguna sebagai bidang boolean? Ini akan menyederhanakan pertanyaan Anda secara signifikan.

SELECT * FROM User WHERE EMAIL_OPT_OUT = false
person Marcus Adams    schedule 20.07.2010
comment
Bukankah saya berharap saya bisa mengubah skemanya. Ini bukan database saya; Saya sebenarnya bergabung dengan pandangan yang keluar dari DB berpemilik. Meskipun saya ingin menunjukkan bahwa pendekatan Anda memiliki kelemahan bahwa serangkaian opsi yang mungkin harus dikodekan dalam skema database. - person Jacob Mattison; 21.07.2010
comment
Hal ini juga akan membuat pemeliharaan saat Anda perlu menambahkan tipe baru menjadi jauh lebih rumit. Tabel terkait seperti yang dimiliki poster adalah desain yang benar. - person HLGEM; 21.07.2010
comment
@HLGEM, saya tidak setuju tentang masalah pemeliharaan, tapi saya menggunakan MVC, yang mengabstraksi semua hal ini. - person Marcus Adams; 21.07.2010