多个表上的 MYSQL JOIN 不返回任何结果 [英] MYSQL JOIN on multiple tables returning no results

问题描述

好吧，让我们看看，我的查询工作正常，但是，只要完成一个朋友帖子.如果用户没有朋友，则不会返回任何结果，这就是我试图掌握的...

Well let's see, the query I have is working fine, as soon as a friendpost is done, however. If the user has no friends, no result will be returned, and that's what I am trying to get a hang of...

    $query = "SELECT DISTINCT m.id, p.byuser, p.`newpost`, p.`id`, p.`postdate`
    FROM users m
    JOIN pinnwand p 
      ON m.id = p.byuser
    JOIN friends f 
      ON f.`userid` = m.id OR f.`friendid` = m.id
    WHERE (f.`userid` = $myId OR f.`friendid`= $myId)
      AND (p.`touser` = p.`byuser` OR p.`touser` = $myId)
      AND p.`publicp` < 3 
      AND p.`typ` = 2
    ORDER BY p.id DESC LIMIT $limit, 10";

我希望有人能帮助我，也许我只是对nao视而不见...

I hope somebody can help me, maybe I am just blind for nao...

编辑由于史蒂文帮了我很多，也许有人发现最后一点遗漏了:它只是显示为特定用户制作的帖子.即使我理解查询，它也应该在他们的钉板上获取朋友发布的帖子?毕竟 m.id 也应该得到朋友表的值，还是我错了?

Edit As Steven helped me out quite a lot, maybe somebody finds the last bit missing: It's just showing the posts made for the specific user. Even though as I understand the query it should get the posts made by friends on their pinboard as well? After all the m.id should get the friendtables value as well, or am I wrong?

编辑 2所以当我现在使用 UNION 和 Subquery 方法时，我仍然想描述结果应该是什么样子:

Edit 2 So as I went with the UNION and Subquery Method for now, I still want to describe what the result should look like:

显示:在任何地方发布的用户帖子、任何人在用户板上发布的帖子、在他们自己的板上发布的朋友帖子！不是人们在朋友板上发布的帖子.

Show: Userposts made whereever, Posts by whomever made on the Userboard, Friendposts made on their own board! Not the posts made by people on friends boards.

推荐答案

有2个问题:

1. 您需要对朋友进行LEFT JOIN.LEFT JOIN 表示即使在连接的第二个表中没有找到结果，也要返回连接中第一个表中的所有记录.您还应该将与 friends 相关的 WHERE 子句条件放入 LEFT JOIN 子句中，以便条件发生在连接处.您还应该在连接中尽可能使用 m.id 而不是 $myId 以消除冗余.
2. 您的 WHERE 子句过于严格(冗余条件).始终尽可能使用最简单的条件集，并在 JOIN 中放置尽可能多的条件，以便于阅读.

1. You need a LEFT JOIN on friends. A LEFT JOIN says to return all records from the first table in the join even if there are no results found in the second table in the join. You also should the WHERE clause conditions relating to friends into the LEFT JOIN clause, so that the conditions occur at the join. You should also be using m.id wherever possible in your joins instead of $myId to eliminate redundancy.
2. Your WHERE clause is too restrictive (redundant conditions). Always use the simplest set of conditions possible, and put as many as appropriate at the JOIN so they are easier to read.

示例(已编辑以添加朋友的帖子):

Example (Edited to add posts from friends, as well):

$query = "SELECT DISTINCT `u`.`id`, `p`.`byuser`, `p`.`newpost`, `p`.`id`, `p`.`postdate`
          FROM `users` AS `u`
          LEFT JOIN `friends` AS `f` 
              ON `f`.`userid` = `u`.`id` 
              OR `f`.`friendid` = `u`.`id`
          JOIN `pinnwand` AS `p` 
              /* This will get all posts made by the user */
              ON `p`.`byuser` = `u`.`id` 
              /* This will get all posts made TO the user by friends */
              OR (`p`.`byuser` IN (`f`.`userid`, `f`.`friendid`)
                  AND `p`.`touser` = `u`.`id`)
          WHERE `u`.`id` = {$myId}
              AND `p`.`publicp` < 3 
              AND `p`.`typ` = 2
          ORDER BY `p`.`id` DESC 
          LIMIT {$limit}, 10";

这篇关于多个表上的 MYSQL JOIN 不返回任何结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！