MySQL连接排除某些记录?
我正在尝试构建一个连接,但我无法让它在 MySQL 中工作,我知道一定有办法。
我有两个表:1。 设置
和2。 User_Setting
(当然还有User
,但这里不需要)。
结构
Setting.setting_id Setting.description Setting.value_type Setting.default_value User_Setting.user_id User_Setting.setting_id User_Setting.value
我想选择所有Setting
记录,并在有值的地方加入User_Setting
。 我相信经典的外连接吗?
我有这样的查询:
SELECT `User_Setting`.`user_id`, `User_Setting`.`value`, `Setting`.`setting_id`, `Setting`.`default_value`, `Setting`.`value_type`, FROM `User_Setting` Right Join `Setting` ON `User_Setting`.`setting_id` = `Setting`.`setting_id` WHERE `User_Setting`.`user_id` IS NULL OR `User_Setting`.`user_id` = 1;
但是,如果 user_id
2 的 User
具有特定 setting_id 的
,但 User_Setting
记录,则此查询不起作用user_id
1 的 User
尚未存储该 setting_id
记录...
有人有查询将检索所有设置,如果 User_Setting
记录不存在,则 user_id
和 value
为 NULL?
i'm trying to build a join, but I can't get it to work in MySQL, I know there must be a way.
I have two tables: 1. Setting
and 2. User_Setting
(and ofcourse User
but that won't be needed here).
Structure
Setting.setting_id Setting.description Setting.value_type Setting.default_value User_Setting.user_id User_Setting.setting_id User_Setting.value
I want to select ALL Setting
records, and join User_Setting
where there is a value. Classical outer join i would believe?
I have this query:
SELECT `User_Setting`.`user_id`, `User_Setting`.`value`, `Setting`.`setting_id`, `Setting`.`default_value`, `Setting`.`value_type`, FROM `User_Setting` Right Join `Setting` ON `User_Setting`.`setting_id` = `Setting`.`setting_id` WHERE `User_Setting`.`user_id` IS NULL OR `User_Setting`.`user_id` = 1;
But this doesn't work if a User
with user_id
2 has a User_Setting
record present for a certain setting_id
, but the User
with user_id
1 hasn't got that setting_id
record stored...
Does anybody have a query where ALL settings will be retrieved, with user_id
and value
NULL if the User_Setting
record doesn't exist?
这个(如果你想检查用户是否存在)
或这个(如果你不想检查用户是否存在):
This (if you want to check that the user exists)
or this (if you don't want to check that the user exists):