MySQL连接排除某些记录?

发布于 07-30 04:32 字数 1138 浏览 4 评论 0原文

我正在尝试构建一个连接,但我无法让它在 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_idvalue 为 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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

阳光①夏2024-08-06 04:32:53

这个(如果你想检查用户是否存在)

SELECT  `User_Setting`.`user_id`,
        `User_Setting`.`value`,
        `Setting`.`setting_id`,
        `Setting`.`default_value`,
        `Setting`.`value_type`,
FROM    user
CROSS JOIN
        setting
LEFT JOIN
        user_setting
ON      user_setting.user_id = user_id
        AND user_setting.setting_id = setting.setting_id
WHERE   user_id = 1

或这个(如果你不想检查用户是否存在):

SELECT  `User_Setting`.`user_id`,
        `User_Setting`.`value`,
        `Setting`.`setting_id`,
        `Setting`.`default_value`,
        `Setting`.`value_type`,
FROM    setting
LEFT JOIN
        user_setting
ON      user_setting.user_id = 1
        AND user_setting.setting_id = setting.setting_id

This (if you want to check that the user exists)

SELECT  `User_Setting`.`user_id`,
        `User_Setting`.`value`,
        `Setting`.`setting_id`,
        `Setting`.`default_value`,
        `Setting`.`value_type`,
FROM    user
CROSS JOIN
        setting
LEFT JOIN
        user_setting
ON      user_setting.user_id = user_id
        AND user_setting.setting_id = setting.setting_id
WHERE   user_id = 1

or this (if you don't want to check that the user exists):

SELECT  `User_Setting`.`user_id`,
        `User_Setting`.`value`,
        `Setting`.`setting_id`,
        `Setting`.`default_value`,
        `Setting`.`value_type`,
FROM    setting
LEFT JOIN
        user_setting
ON      user_setting.user_id = 1
        AND user_setting.setting_id = setting.setting_id
烟织青萝梦2024-08-06 04:32:53

我通常向 WHERE 子句添加一个条件,以检查要连接的表中的主 if 不为空。

I usually add a condition to the WHERE clause to check that the primary if in the table being joined is not null.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文