mysql query using joins

Question

I'm trying to write a query that selects the latest "board" entries posted by friends, as well as the particular user. Meaning, if I was the user viewing this section, it would show the latest 6 board items including mine if I had posted something.

The query to get the friends is:

SELECT b.id, b.user, b.title, b.text, b.time
FROM board b
JOIN user u ON u.user = b.user
JOIN friend f ON f.friend_id = u.id
WHERE f.user_id = 1
ORDER BY `time` DESC
LIMIT 0 , 6

I'm dealing with tables where the actual user name was used in some tables, the user id in others. Thus, why the joins. the above query gets all the latest posts by friends, but I want the user's own posts included if they made one. For example:

SELECT b.id, b.user, b.title, b.text, b.time
FROM board b
JOIN user u ON u.user = b.user
JOIN friend f ON f.friend_id = u.id
WHERE f.user_id = 1 
OR b.user = 'the_users_name' 
ORDER BY `time` DESC
LIMIT 0 , 6

The second query merely returns 6 duplicate rows of the user due to the joins. How can I fix this so it shows any user posts, and any friend posts?

Answer 1

Use a UNION: http://dev.mysql.com/doc/refman/5.0/en/union.html

SELECT b.id, b.user, b.title, b.text, b.time
FROM board b
JOIN user u ON u.user = b.user
JOIN friend f ON f.friend_id = u.id
WHERE f.user_id = 1
UNION ALL
SELECT b.id, b.user, b.title, b.text, b.time
FROM board b
JOIN user u ON u.user = b.user
JOIN friend f ON f.friend_id = u.id
WHERE f.user_id = 1 
OR b.user = 'the_users_name' 
ORDER BY `time` DESC
LIMIT 0 , 6

Something like that?

Answer 2

I think using UNION ALL should do the trick:

SELECT b.id, b.user, b.title, b.text, b.time
FROM board b
JOIN user u ON u.user = b.user
JOIN friend f ON f.friend_id = u.id
WHERE f.user_id = 1
UNION ALL
SELECT b.id, b.user, b.title, b.text, b.time
FROM board b
JOIN user u ON u.user = b.user
WHERE u.user_id = 1
ORDER BY time DESC
LIMIT 0,6

The first query should give you the friends posts and the second would give you the users posts. The UNION ALL combines those results together and the order by & limit are applied to the combined results.