我使用哪种 JOIN 类型在此查询中显示 NULL 值? [英] Which JOIN type do I use to display NULL values in this query?
问题描述
我正在尝试加入三个表.
I'm trying to join THREE tables.
第一个表格是transactions
,其中包含学生获得的积分"的详细信息.这里的相关字段是Recipient_ID
(学生接收点).
The first table is transactions
which contains details of "points" that students have earned. The relevant field here is Recipient_ID
(the student receiving points).
第二个表是purchases
,其中包含学生购买的奖励"的详细信息.此处的相关字段是 Student_ID
和 Reward_ID
.
决赛桌是rewards
,其中详细说明了购买的奖励.这里的相关字段是 Reward_ID
、Cost_to_User
(学生为每个奖励支付的价格;我目前根本没有使用它)和 Title
.
The final table is rewards
which details the rewards purchased. The relevant fields here are Reward_ID
, Cost_to_User
(the price the students pay for each reward; I'm currently not using this at all) and Title
.
我想要做的是,提供特定的学生 ID 列表,显示学生的积分列表和三个最昂贵的奖励购买.
What I'm trying to do, provided with a specific list of student IDs, is display a list of students' points AND three most expensive reward purchases.
到目前为止我编写的 SQL 语句是:
The SQL statement I've written thus far is:
SELECT
t.Recipient_ID AS `ID` ,
SUM( t.Points ) AS `Points Earned`,
SUBSTRING_INDEX( GROUP_CONCAT(DISTINCT r.Title SEPARATOR ', '),', ',3 ) as `Rewards`
FROM `transactions` t
JOIN `purchases` p ON t.Recipient_ID = p.Student_ID
JOIN `rewards` r ON p.Reward_ID = r.Reward_ID
WHERE T.`Recipient_ID`
IN ( 90128, 90163, 34403, 35501 )
GROUP BY t.`Recipient_ID`
这在一定程度上有效 - 显示了两名学生,以及他们的总积分和最新奖励.
This works to an extent - two students are displayed, with their point totals and latest rewards.
然而,他们的总点数是大错特错的,我相信如果学生没有购买任何东西,他们就不会显示.
However, their point totals are vastly wrong, and I believe students aren't displaying if they haven't made any purchases.
如果学生没有购买,我仍然想显示他们.
我怀疑是我的 JOIN 不正确,但我也怀疑 GROUP_CONCAT
不够强大,无法列出三个最昂贵的奖励.我需要更改我的 JOIN,还是需要使用某种子查询?
I suspect it's my JOINs which are incorrect, but I also suspect GROUP_CONCAT
isn't powerful enough to list the three most EXPENSIVE rewards. Do I need to change my JOINs, or do I need to use some sort of subquery?
提前致谢,
推荐答案
我认为您应该考虑使用 LEFT JOIN
(参见 联接的可视化说明):
I think you should look at using a LEFT JOIN
(See Visual Explanation of Joins):
SELECT
t.Recipient_ID AS `ID` ,
SUM( t.Points ) AS `Points Earned`,
SUBSTRING_INDEX( GROUP_CONCAT(DISTINCT r.Title SEPARATOR ', '),', ',3 ) as `Rewards`
FROM `transactions` t
LEFT JOIN `purchases` p
ON t.Recipient_ID = p.Student_ID
LEFT JOIN `rewards` r
ON p.Reward_ID = r.Reward_ID
WHERE T.`Recipient_ID`
IN ( 90128, 90163, 34403, 35501 )
GROUP BY t.`Recipient_ID`
LEFT JOIN
将返回 transactions
表中的所有记录,即使 purchases
或 rewards<中没有记录/code> 表格.
A LEFT JOIN
will return all records from the transactions
table even if there are no records in the purchases
or rewards
table.
这篇关于我使用哪种 JOIN 类型在此查询中显示 NULL 值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!