Showing "subscriber" Posts And User's Own Posts

This has stumped me for some time. My problem: I have 2 different tables.. A table for user posts and a table for subscribers. The subscribers table looks like this: SubscriberID

Solution 1:

You can do it modifying your query to :

SELECT POSTS.*
FROM POSTS
LEFT JOIN SUBSCRIBERS
ON POSTS.AUTHORID = SUBSCRIBERS.PROFILEID
WHERE SUBSCRIBERS.SUBSCRIBERID = ? OR POSTS.AUTHORID = ?
GROUPBY POSTS.POSTID ORDERBY POSTS.POSTID DESC LIMIT 10

It selects the user own posts as well. Hope this would help.

Updated : Added GROUP BY POSTS.POSTID so duplicates are removed as you only look for data in POSTS table.

When you run query like passing values- Eg. for user having id 1 the query looks like :

SELECT POSTS.*
FROM POSTS
LEFT JOIN SUBSCRIBERS
ON POSTS.AUTHORID = SUBSCRIBERS.PROFILEID
WHERE SUBSCRIBERS.SUBSCRIBERID = 1OR POSTS.AUTHORID = 1GROUPBY POSTS.POSTID
ORDERBY POSTS.POSTID DESC LIMIT 10

Results are :

PostIDAuthorIDPostDatePostBody312012-12-21  OhWait232012-12-21  ByeByeWorld122012-12-20  HelloWord

This is what you get when pass values to the select query properly. The values passed to SUBSCRIBERID and AUTHORIDshould be same. The LEFT JOIN would fix your problem.