mysql - 多个连接的奇怪 SQL 问题

Question

我目前正在为应用程序构建 API，但在处理特定查询时遇到了一些问题。

这是我的原始查询:

SELECT `users`.`id`, 
       unclaimed_users.id AS unclaimed_id, 
       `users`.`firstname`, 
       `users`.`lastname`, 
       `unclaimed_users`.`mobile_number`, 
       `group_members`.`status`, 
       `user_images`.`profile_image_main` 
FROM   `group_members` 
       LEFT JOIN `users` 
              ON `users`.`id` = `group_members`.`user_id` 
                 AND `group_members`.`unclaimed_user` = 0 
       LEFT JOIN `user_images` 
              ON `user_images`.`id` = `group_members`.`user_id` 
                 AND `group_members`.`unclaimed_user` = 0 
       LEFT JOIN `unclaimed_users` 
              ON `unclaimed_users`.`id` = `group_members`.`user_id` 
                 AND `group_members`.`unclaimed_user` = 1 
WHERE  `group_members`.`group_id` = 1 
ORDER  BY `group_members`.`created_at` ASC

当我执行上面的查询时，我收到了我期望的两条记录，但是，从 unclaimed_user 表中提取的任何字段都返回一个空字符串(不是 null)，而不是返回值.

看这里:

+------+--------------+-----------+----------+---------------+---------+--------------------+
| id   | unclaimed_id | firstname | lastname | mobile_number | status  | profile_image_main |
+------+--------------+-----------+----------+---------------+---------+--------------------+
|    1 |         NULL | Ben       | Carey    | NULL          | active  | NULL               |
| NULL |            0 | NULL      | NULL     |               | pending | NULL               |
+------+--------------+-----------+----------+---------------+---------+--------------------+

但是，当我将 SELECT 替换为 SELECT * 时，它会返回所有预期的字段以及预期的值。例如，mobile_number 返回与相关无人认领用户关联的手机号码。

我做错了什么？我无法弄清楚正在发生的事情以及为什么没有返回值。

我试过删除所有字段，只包含无人认领用户表中的一个字段，例如

SELECT unclaimed_users.id FROM etc...

上面返回了0，但是应该返回1...

重要的是要注意 id 和 mobile_number 都出现在 users 表和 unclaimed_users 中表。

更新

我目前正在整理 fiddle ，但事实证明这比我想象的要难，因为我还没有成功重现问题...

与此同时，我简化了仍未返回其含义的查询:

SELECT `unclaimed_users`.`mobile_number` 
FROM   `group_members` 
       LEFT JOIN `unclaimed_users` 
              ON `unclaimed_users`.`id` = `group_members`.`user_id` 
                 AND `group_members`.`unclaimed_user` = 1 
WHERE  `group_members`.`group_id` = 1 

以上返回以下内容

+---------------+
| mobile_number |
+---------------+
| NULL          |
|               |
+---------------+

何时应该返回:

+---------------+
| mobile_number |
+---------------+
| NULL          |
| +447777779999 |
+---------------+

如果我将 SELECT unclaimed_users.mobile_number 替换为 SELECT *，则会返回正确的数据，因此连接正常。我不明白为什么我无法引用字段 mobile_number

更新2

我注意到当我执行以下命令时它起作用了:

SELECT `unclaimed_users`.`mobile_number` 
FROM   `group_members` 
       LEFT JOIN `unclaimed_users` 
              ON `unclaimed_users`.`id` = `group_members`.`user_id` 
                 AND `group_members`.`unclaimed_user` = 1 
WHERE  `group_members`.`unclaimed_user` = 1 -- This is the bit I changed

因为上面忽略了表中的第一条记录(unclaimed_user=0 的记录，它似乎工作...

Answer 1

因为你没有提供太多来测试...

SELECT `users`.`id`, 
   unclaimed_users.id AS unclaimed_id, 
   `users`.`firstname`, 
   `users`.`lastname`, 
   `unclaimed_users`.`mobile_number`, 
   `group_members`.`status`,
   `group_members`.`user_id`,
   `group_members`.`unclaimed_user`,
   `user_images`.`profile_image_main` 
FROM   `group_members` 
   LEFT JOIN `users` 
          ON `users`.`id` = `group_members`.`user_id` 
             AND `group_members`.`unclaimed_user` = 0 
   LEFT JOIN `user_images` 
          ON `user_images`.`id` = `group_members`.`user_id` 
             AND `group_members`.`unclaimed_user` = 0 
   LEFT JOIN `unclaimed_users` 
          ON `unclaimed_users`.`id` = `group_members`.`user_id` 
             AND `group_members`.`unclaimed_user` = 1 
WHERE  `group_members`.`group_id` = 1 
ORDER  BY `group_members`.`created_at` ASC

我不确定是否是一个好的答案，我只是不能添加评论。