Need help on creating a mysql query

Question

I have three tables - 1-FriendShip, 2-Users, 3-Pages. Friendship table has fromUser, and toUser columns. fromUser column contains the user who initiated friend request. toUser contains who received friend request (Inspired from Facebook)

I want to select data from Pages table who are friends with a given user, say 'user1'. I can get the friends of user1 using this query:

  SELECT friends.`fromUser` AS `friend`
    FROM friends
    INNER JOIN users
    ON friends.`fromUser` = users.`userid`
    WHERE `friends`.`toUser`='user1'
 UNION
  SELECT friends.`toUser` AS `friend`
    FROM friends
    INNER JOIN users
    ON friends.`toUser` = users.`userid`
    WHERE friends.`fromUser`='user1'

This gives me result like this:

Friends
user2
user5

and so on...

I want to select the pages of all these users and order them in descending order by timestamp column. Anyone please provide a solution?

Answer 1

You can use IN, assuming your Pages table has a userid (or equivalent) column:

SELECT * FROM Pages
WHERE userid IN (
   -- Your query to retrieve friends of user1
)
ORDER BY `timestamp` DESC