我使用哪种 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屋！