How to query multiple tables?

Question

What I want to achieve is to select the comments which are written only by my friends. I have the following tables.

Friends

id
my_user_id
friend_user_id

Comments

id
user_id
comment

This is how I did so far.

First find out who are my friends in the friends table:

mysql_query("SELECT friend_user_id FROM friends 
             WHERE my_user_id = $user_id ");

Then I selected the comments written only by my friends.

mysql_query("SELECT * FROM comments WHERE user_id = 'my_first_friends_id' 
             OR user_id = 'my_second_friends_id' ");

Now, this was the lame version, and it is very slow. I have millions of entries in the comments table.

What is the best solution for this problem? How to solve this in one query? And also very important to be fast.

Joe Stefanelli · Accepted Answer · 2011-02-10 21:18:25Z

3

select c.id, c.user_id, c.comment
    from Friends f
        inner join Comments c
            on f.friend_user_id = c.user_id
    where f.my_user_id = $user_id

answered Feb 10, 2011 at 21:18

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

14 Comments

Maxxon Over a year ago

Thanks, I will try it out now. What is the c. and f. ?

Dan J Over a year ago

@Maxxon those are table aliases, which allow you to refer to columns on those tables without repeatedly typing each table name.

Parris Varney Over a year ago

Make sure friend_user_id and user_id are indexed, btw. The c and f are table aliases, so you don't have to write "Friends" over and over again.

Maxxon Over a year ago

Thanks, I see You have changed c.* to c.id, c.user_id, c.comment. Will it work with c.* ?

Joe Stefanelli Over a year ago

@Maxxon: c and f are aliases for the Comments and Friends table. It's just a short-hand way of referring to the tables.

|

Collectives™ on Stack Overflow

How to query multiple tables?

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related