0
  SELECT p.*
    FROM StatusUpdates p
    JOIN FriendRequests fr
      ON (fr.From = p.AuthorId OR fr.To = p.AuthorId)
   WHERE fr.To = ".$Id." OR fr.From = ".$Id." 
     AND fr.Accepted = 1
ORDER BY p.DatePosted DESC

I'm using this SQL code at the moment which somone wrote for me on a different question. I'm using PHP, but that shouldn't make much difference, since the only thing I'm doing with it is concatenating a variable into it.

What it's meant to do is go through all your friends and get all their status posts, and order them. It works fine, but it picks out "$Id"'s posts either not at all, or the amount of friends you have

Eg, if you had 5 friends, it would pick our your posts 5 times. I only want it to do this once. How could I do this?

Ashley Davies
  • 1,873
  • 1
  • 23
  • 42
  • Yes (Have to post more characters to get past minimum of 15 character limit) – Ashley Davies Jun 03 '11 at 15:18
  • a better technique than padding a 'yes' comment is to edit your question and (a) mention the DBMS (MySQL) in the question, and (b) add a mysql tag. And if you must pad, you could write: "Yes, I am using MySQL" which gets you past the limit. – Jonathan Leffler Jun 03 '11 at 15:22
  • Ok, I'll do that in future. Apologies, I couldn't think of anything else to put at the time. – Ashley Davies Jun 03 '11 at 15:36

2 Answers2

1

Either GROUP BY p.id or SELECT DISTINCT p.id, p.*

Brad
  • 5,428
  • 1
  • 33
  • 56
  • Thanks, implemented and works. It still only picks the user's posts if they have at least 1 friend, though. Is there any easy fix to get around this, or would it be easier to trick the system by giving everyone a fake friend? – Ashley Davies Jun 03 '11 at 15:22
1

You need to use LEFT JOIN instead of join if you want to display post id regardless of number of friends. And GROUP BY p.id in order not to display the same posts more than 1 time:

SELECT p.*
FROM StatusUpdates p
LEFT JOIN FriendRequests fr
  ON ((fr.From = p.AuthorId OR fr.To = p.AuthorId) AND fr.Accepted = 1)
 WHERE p.AuthorId = ".$Id."

GROUP BY p.id
ORDER BY p.DatePosted DESC
a1ex07
  • 36,826
  • 12
  • 90
  • 103