从两个表中选择并从两个“朋友列表”中进行检查

Question

Background info - I have 4 tables in total.

• Posts - Posts from normal users
• Group posts - Posts from groups
• Friends list - Stores friendships
• Group memberships - Stores group memberships

What I'm trying to achieve is: the page will list all posts from yourself, your friends and the groups you join.

(Previous query) - Posts & Group posts were in the same table, and identified by the 'type' field

SELECT
      DISTINCT p.id,
      p.type, 
      p.user1, 
      p.user2, 
      p.content, 
      p.timestamp,
      p.comments
FROM posts p, friends f, group_memberships g
WHERE (p.user1 = f.user2 AND f.user1 = 'MY ID') OR
       p.user1 = 'MY ID' OR 
      (p.user1 = g.group_id AND g.user = 'MY ID')
ORDER BY p.timestamp, DESC LIMIT 10

Previously, I had posts and group_posts in the same table. But now, I think they should be in two different tables.

(NEW query) - Where posts and group posts are in separate tables

SELECT 
      DISTINCT p.id, 
      p.type, 
      p.user1, 
      p.user2, 
      p.content, 
      p.timestamp, 
      p.comments, 
      gp.id, 
      gp.type, 
      gp.user1, 
      gp.user2, 
      gp.content, 
      gp.timestamp, 
      gp.comments
FROM posts p, group_posts gp, friends f, group_memberships g
WHERE (p.user1 = f.user2 AND f.user1 = 'MY ID') OR 
       p.user1 = 'MY ID' OR 
      (p.user1 = g.group_id AND g.user = 'MY ID')
ORDER BY p.timestamp, gp.timestamp DESC LIMIT 10

The results I'm getting is the entire row filled with the same post from my group_posts.

Also, my ids in both posts & group_posts are not in sequence i.e. posts may have 100 posts, but group_posts may only have 30.

What is wrong?

Answer 1

Try this:

SELECT 
      p.id, 
      p.type, 
      p.user1, 
      p.user2, 
      p.content, 
      p.timestamp, 
      p.comments, 
      gp.id, 
      gp.type, 
      gp.user1, 
      gp.user2, 
      gp.content, 
      gp.timestamp, 
      gp.comments
FROM posts p, group_posts gp, friends f, group_memberships g
WHERE (p.user1 = f.user2 AND f.user1 = 'MY ID') OR 
       p.user1 = 'MY ID' OR 
      (p.user1 = g.group_id AND g.user = 'MY ID')

GROUP BY p.id
ORDER BY p.timestamp, gp.timestamp DESC LIMIT 10;

Note switching DISTINCT to GROUP BY to group by each post.