join as an alternative to multiple OR's

Question

Hi.

I have two tables with the following structure:

table name: friends
uid (PK)(FK)
friend_id (PK)(FK)

table name: posts
post_id (AI)(PK)
post_text
uid (FK) //poster id

I'd like to select posts that were only made from friends of user x. My inital plan was to use the following code:

SELECT posts.post_text INNER JOIN friends ON posts.uid = friends.friend_id 
WHERE friends.uid = x

However, this doesn't seem to work. (I get all posts done by x, done all posts done by X's friends. The only alternative I can think of is to use a (possibly very long) string of OR's - as in

WHERE posts.uid = 'friend_id_1' OR posts.uid = 'friend_id_2' ect..

Any alternative solutions? Thanks.

Answer 1

SELECT  posts.*
FROM    friends
JOIN    posts
ON      posts.uid = friends.friend_id
WHERE   friends.uid = 'x'

This query should work, so you better check your data.

Didn't you occasionally make X his own friend?

If you friendship relation is symmetrical, i. e. if X is a friend to Y, then Y is a friend to X, then you should make the table as this:

friends (a, b, PRIMARY KEY (a, b), KEY (b))

SELECT  posts.*
FROM    (
        SELECT  b AS friend_id
        FROM    friends
        WHERE   a = 'X'
        UNION ALL
        SELECT  a AS friend_id
        FROM    friends
        WHERE   b = 'X'
        ) friends
JOIN    posts
ON      posts.uid = friends.friend_id

Before inserting a pair of friends, always arrange it so that A < B, i. e. the user with the least user_id goes into A.

Answer 2

I think this is what you are looking for

SELECT posts.post_text INNER JOIN friends ON posts.uid = friends.friend_id 
Inner join friends fr on friends.friend_id = fr.uid
WHERE friends.uid = x