CODEWITHSUNDEEP
mysql
Josh Foskett
Josh Foskett

Reputation: 4121

How to find threads only posted by friends?

So, I have a majority of my SQL query done. However, I can't seem to figure out how select threads that were only posted by the users friends.

Here's my current query that works:

SELECT social_posts.message, social_posts.post_stamp, user.username, user.id, user.default_picture
FROM social_threads
JOIN social_posts ON social_posts.thread_id = social_threads.id
LEFT JOIN user ON user.id = social_threads.user_id
ORDER BY social_threads.id DESC

Here's the friends table: 

*----*--------*--------*---------*
| id | user_a | user_b | request | 
*----*--------*--------*---------*
| 1  | 3      | 5      | 1       |
*----*--------*--------*---------*

request set to one means the friend request was accepted.

The where clause in the query would have to look something like this:

WHERE ((friends.user_a = '3' || friends.user_b = '3') && friends.request = '1') || (social_threads.user_id = '3')

I just need to figure out how to get that where clause in the main query.

Any help is greatly appreciated.

Upvotes: 0

Views: 64

Answers (2)

TPete
TPete

Reputation: 2069

You would have to Join the friends table, to keep only rows, in which the user is a friend of the thread poster. Something like this:

SELECT social_posts.message, social_posts.post_stamp, user.username, user.id, user.default_picture
FROM social_threads
JOIN social_posts ON social_posts.thread_id = social_threads.id
LEFT JOIN user ON user.id = social_threads.user_id
Join friends on (user.id = friends.user_a and social_threads.user_id = friends.user_b or user.id = friends.user_b and social_threads.user_id = friends.user_a) and friends.request = 1
ORDER BY social_threads.id DESC

Upvotes: 1

m1k3y3
m1k3y3

Reputation: 2798

use OR and AND operator 

WHERE (friends.user_a = '3' OR friends.user_b = '3') AND friends.request = '1'

Upvotes: 0

Related Questions