Background info - I have 4 tables in total.
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?
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.