MYSQL JOIN on multiple tables returning no results

Question

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";

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

---

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?

---

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.

Answer 1

There are 2 problems:

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";

Answer 2

OK, so in the end i ended up using an Union and a subquery... It's probably suboptimal, but if somebody has a good suggestion what to improve, please give me your opinion! Otherwhise I hope that this post will help people with simmilar problems.

    $query = "SELECT DISTINCT `p`.`byuser`, `p`.`newpost`, `p`.`id`, `p`.`postdate`
    FROM `pinnwand` AS `p`
    JOIN `users` AS `u` ON `u`.`id` = `p`.`byuser`
    LEFT JOIN `friends` AS `f` ON (`f`.`friendid` = `u`.`id` OR `f`.`userid` = `u`.`id`)
    WHERE (f.userid = {$myId} OR f.friendid = {$myId})
        AND `p`.`publicp` < 3 
        AND `p`.`typ` = 2
        AND `p`.`byuser` <> {$myId}
    UNION ALL
    SELECT DISTINCT `p`.`byuser`, `p`.`newpost`, `p`.`id`, `p`.`postdate`
    FROM `pinwand` AS `p`
    JOIN `users` AS `u` ON `u`.id = `p`.`byuser`
        WHERE `u`.`id` = {$myId}
        AND `p`.`publicp` < 3 
        AND `p`.`typ` = 2
    ORDER BY `postdate` DESC
    LIMIT 0,10";