将 3 个 MySQL 表加入 1 个查询 [英] Joining 3 MySQL tables in to 1 query

问题描述

我有 3 个表，用于存储用户、可用设置和用户设置.用户表具有标准的用户信息，如用户名、密码、姓名、日期等.设置表包含所有可用系统设置的列表和默认值，例如:

I have 3 tables which store user, available settings and user settings. User table has standard user stuff like username, password, name, date etc. Settings table has a list of all available system settings and a default value like:

settingID   name                  defaultValue
----------------------------------------------
1           push notifications    1
2           profile image         0
3           full name             1
4           distance measure      Miles

用户设置表具有用户设置的任何自定义设置，如下所示:

User setting table has any custom settings set by the user like so:

userSettingID    userID    settingID    customValue
----------------------------------------------------------
1                1         1            0
2                1         3            0
3                1         4            KM

我正在尝试进行一个查询，将所有 3 个表连接在一起，但是从设置表中生成所有结果，如果在 userSettings 表中有匹配项，请使用 customValue，否则使用设置表中的 defaultValue.

I am trying to make a query that will join all 3 tables together however produce ALL results from the settings table and if there is a match in the userSettings table use the customValue otherwise use the defaultValue from the settings table.

所以我想制作以下内容:

So I would like to produce the below:

userID   settingID   userSettingID   name     defaultValue   customValue
---------------------------------------------------------------------------
1        1           1               push...  1              0
null     2           null            profi..  0              null
1        3           2               full...  1              0
1        4           3               dista..  Miles          KM

如您所见，我希望每个设置记录，如果有匹配项填充 userID 和/或 customValue，否则填充为 NULL.

As you can see I would like EVERY setting record and if there are matches populate with userID and/or customValue otherwise fill with NULL.

我能够得到想要的结果，但是一旦我添加了 WHERE userID = x 子句，我只会得到用户设置的记录.

I was able to get the desired results but as soon as I add a WHERE userID = x clause I only get records that a user has been set to.

谢谢

到目前为止我的查询是:

The query I have so far is:

SELECT *
FROM settings
LEFT OUTER JOIN userSettings ON settings.settingID = userSettings.settingID
LEFT OUTER JOIN user ON userSettings.userID = user.userID

推荐答案

要获取 customValue 而不是 defaultValue(如果存在)，您可以使用 合并 函数并按 userid 过滤，您需要将条件添加到连接中，而不是在 where 子句中使用它(这会使左连接表现得像内部连接).

To get the customValue instead of the defaultValue if it is present you can use the coalesce function and to filter by userid you'll want to add the condition to the join instead of using it in a where clause (which would make the left join behave like an inner join).

我猜你想要这样的东西:

I'm guessing you want something like this:

select 
    us.userID, 
    s.settingID, 
    us.userSettingID,
    s.name, 
    s.defaultValue,
    us.customValue,
    coalesce(us.customValue, s.defaultvalue) as OverriddenValue
from settings s
left join Usersetting us on s.settingID = us.settingID 
left join user u on u.userid = us.userID and u.userID = 1

user 是 MySQL 中的非保留关键字，因此不是标识符的好选择，尽管它可以不带引号使用.

user is a non-reserved keyword in MySQL and as such isn't a good choice for an identifier, although it is permitted to be used without quoting.

这篇关于将 3 个 MySQL 表加入 1 个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！