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?

Eric Petroelje · Accepted Answer · 2011-08-11 20:51:15Z

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.

answered Aug 11, 2011 at 20:51

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rbruhn Over a year ago

Thanks. Never even thought of the union!! I changed it so the second query did not user the join since I'm using "user" to select from the board: select * from board where user = 'the_user_name'

Pelshoff · Accepted Answer · 2011-08-11 20:53:01Z

0

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?

answered Aug 11, 2011 at 20:53

Pelshoff

1,47410 silver badges14 bronze badges

Collectives™ on Stack Overflow

mysql query using joins

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related