Selecting a user's friend's events

Question

I have these information :
Table "Users" =>

 **Id**     **Name** 
   1           a
   2           b
   3           c
   4           d
   5           e

Table "Friends" =>

**SenderId**  **ReceiverId**    **State**
     1              2              x 
     2              3              ok 
     3              1              ok
     3              4              ok
     5              3              ok 
     5              4              ok

Table "Events" =>

     **SenderId**    **receiverId**     **text**
 1        1                3              ssss
 2        3                1             dsadsa
 3        2                3             safsdf
 4        3                5              fgfdgfd
 5        4                3             fgfhgfh
 6        5                4              sad sad

My question is that how could I get the events of user's friends in one sql statement using JOINS only .

for example :=>

• userId : 1
• his friends : 3 (state = ok)
• friends events : 1, 2, 3, 4, 5 (events from 1 to 5 have the userId 3 which considered as a friend to user 1 )

ANY HELP .. THANKS ;) !!

Answer 1

Your table structure is quite confusing with all those Sender and Receiver ID's, but I think the following query will work.

SELECT * FROM
(SELECT Users.Name, Events.text 
   FROM Users  
   LEFT JOIN Friends ON Users.Id = Friends.ReceiverId 
   LEFT JOIN Events ON Friends.SenderId = Events.SenderId 
   WHERE Users.Id = 1)
UNION ALL
(SELECT Users.Name, Events.text 
   FROM Users 
   LEFT JOIN Friends ON Users.Id = Friends.ReceiverId 
   LEFT JOIN Events ON Friends.SenderId = Events.ReceiverId 
   WHERE Users.Id = 1)

You can probably simplify this, but I'm not sure how, considering you want to select events 1-5 because the friend ID can be either the Events.SenderId or the Events.ReceiverId.

I don't know if SQL supports this, but maybe put an OR into the LEFT JOIN clause (?):

SELECT Users.Name, Events.text 
  FROM Users 
  LEFT JOIN Friends ON Users.Id = Friends.ReceiverId 
  LEFT JOIN Events ON (Friends.SenderId = Events.SenderId OR Friends.SenderId  = Events.ReceiverId) 
  WHERE Users.Id = 1

Answer 2

It's pretty easy with a sub-query.

Here's the subquery (find user 1's friends)

SELECT
   senderID
FROM
   friends
WHERE
   receiverID = 1

Here's the main query (find user1's friend's events)

SELECT
   primary_key,
   senderID,
   receiverID,
   text
FROM
   events
WHERE
      senderID IN (subquery) --events where user1's friends were the senders
   OR receiverID IN (subquery) --events where user1's friend's were the receivers

Put it all together:

SELECT
   *
FROM
   events
WHERE
      senderID IN (SELECT
                      senderID
                   FROM
                      friends
                   WHERE
                      senderID = 1) --events where user1's friends were the senders
   OR receiverID IN (SELECT
                      senderID
                   FROM
                      friends
                   WHERE
                      receiverID = 1) --events where user1's friend's were the receivers

You might consider replacing the explicit '1' with a variable, so you can run this query for every user.

Good luck

Answer 3

You didn't specify a name for the first column in your Events table so I've called it row_ID:

SELECT U1.ID AS user_ID, U1.Name AS user_name, 
       U2.ID AS friend_user_ID, U2.Name AS friend_user_name, 
       E1.row_ID, E1.text AS event_text
  FROM Users AS U1
       INNER JOIN Friends AS F1
          ON (
              U1.ID = F1.ReceiverId
              OR U1.ID = F1.SenderId
             )
             AND F1.State = 'ok'
       INNER JOIN Users AS U2
          ON U2.ID = F1.SenderId
       INNER JOIN Events AS E1
          ON (
              U2.ID = E1.ReceiverId
              OR U2.ID = E1.senderId
             )
 WHERE U1.ID = 1;