I have a join that selects all of the posts from users I am following and friends with
SELECT *
FROM posts a
LEFT JOIN relationships b
ON a.user_id = b.user_2
WHERE b.user_1 = $user_id AND
b.status IN (1,3,4)
How can I get this query to also select all of the posts where a.user_id = $user_id (my id)? Basically, also select all of the posts from me? Without duplicating any data.
If you basically want the results of 2 queries in one table use a union query. You would need to fix the WHERE clause in the 2nd query to be correct but what about something like
SELECT *
FROM posts a
LEFT JOIN relationships b
ON a.user_id = b.user_2
WHERE b.user_1 = $user_id AND
b.status IN (1,3,4)
UNION DISTINCT
SELECT *
FROM posts a
WHERE a.user_id = $user_id (my id)
I would also question your use of a left join - I would think that an INNER JOIN would be better
I don't have any way to test this, but I would assume the below would work as expected:
SELECT *
FROM posts a
LEFT JOIN relationships b
ON a.user_id = b.user_2
WHERE b.user_1 = $user_id AND
(b.status IN (1,3,4) OR a.user_id = $user_id)