Help with a nested query !

        int id = 1;
        string chain = "(";
        SqlDataReader dr = SqlHelper.ExecuteReader(string.Format("SELECT a.Id as x, c.Id as y From Friends b INNER JOIN Users a ON b.SenderId = a.Id INNER JOIN Users c ON b.ReceiverId = c.Id WHERE (c.Id = {0} OR a.Id = {0}) AND State = '{1}'", id, "ok"));
        if (dr.HasRows)
            while (dr.Read())
                if (id == int.Parse(dr["y"].ToString()))
                    chain += dr["x"].ToString() + ", ";
                else
                    chain += dr[开发者_运维百科"y"].ToString() + ", ";
        if (chain.Length > 1)
            chain = chain.Substring(0, chain.Length - 2) + ")";
        else
            chain = "(0)";
        // Chain has for example  => (2, 3, 4, 5) => which are the Ids for Users's Friends 
        string str = "SELECT TOP 20 a.*, b.UserName as Sender, c.UserName as Receiver, b.Avatar as SenderPic";
        str += " FROM Events a INNER JOIN Users b ON a.SenderId = b.Id INNER JOIN Users c ON a.ReceiverId = c.Id ";
        str += "WHERE SenderId IN ";
        str += chain;
        str += " OR ReceiverId IN";
        str += chain;
        str += " Order BY Id desc";
        dr = SqlHelper.ExecuteReader(str);

chain considered as the user's friends . does any one know how to execute this query with JOINS !? Thanks a lot ..

First, try to get rid of pushing parameter values with string.Format into SQL queries. That's a huge security issue.

To your query. I have to admit, I'm a bit lost in your string concatenation :-P, but if I'm right, you want to get the Events and some user information. As far as I can see, SenderId and ReciverId are already your ids. If so, you can completely remove the first SELECT and provide id (as parameter) directly into your second SQL statement like this (only the SQL):

Edit: Tom showed me the missing part (Status=Ok)

Corrected version:

; WITH OkUsers AS (
   SELECT
      u.*
   FROM Users u
      JOIN Friends f ON u.Id = f.SenderId OR u.Id = f.RecipientId
   WHERE
      f.Status = 'Ok'
)
SELECT TOP(20)
   a.*
   ,b.UserName as Sender
   ,c.UserName as Receiver
   ,b.Avatar as SenderPic
FROM Events a 
   INNER JOIN OkUsers b ON a.SenderId = b.Id 
   INNER JOIN OkUsers c ON a.ReceiverId = c.Id 
WHERE
   a.SenderId = @id
   OR a.ReceiverId = @id;

SELECT distinct TOP(20) e.*, u1.UserName As Sender,
u2.UserName As Receiver, u1.Avatar AS SenderPic  
FROM Friends f INNER JOIN Users u 
ON(u.Id = f.SenderId OR u.Id = f.ReceiverId) AND State = 'ok' 
INNER JOIN Events e 
ON(f.SenderId = e.SenderId OR f.SenderId = e.ReceiverId 
OR f.ReceiverId = e.SenderId OR f.ReceiverId = e.ReceiverId) 
INNER JOIN Users u1 
ON (e.SenderId = u1.Id) 
INNER JOIN Users u2 
ON (e.ReceiverId = u2.Id) 
WHERE u.Id = @id;

I think that might help:

--step[1]
SELECT DISTINCT CASE
WHEN a.Id = 1 THEN c.ID
WHEN c.Id = 1 THEN a.Id
ELSE
0 
END AS ID
INTO #OkUsers
From Friends b INNER JOIN Users a 
ON b.SenderId = a.Id INNER JOIN Users c 
ON b.ReceiverId = c.Id 
WHERE (c.Id = @id OR a.Id = @id) AND State = 'Ok';

--step[2]
SELECT TOP 20 a.*, b.UserName as Sender, c.UserName as Receiver, b.Avatar as SenderPic
FROM Events a INNER JOIN Users b ON a.SenderId = b.Id INNER JOIN Users c ON a.ReceiverId = c.Id
INNER JOIN #OkUsers ON #OkUsers.ID = SenderId OR #OkUsers.ID = ReceiverId
EXCEPT
SELECT TOP 20 a.*, b.UserName as Sender, c.UserName as Receiver, b.Avatar as SenderPic
FROM Events a INNER JOIN Users b ON a.SenderId = b.Id INNER JOIN Users c ON a.ReceiverId = c.Id
--INNER JOIN #OkUsers ON #OkUsers.ID = SenderId OR #OkUsers.ID = ReceiverId
WHERE SenderId IN (SELECT ID FROM #OkUsers) 
    OR ReceiverId IN(SELECT ID FROM #OkUsers);

DROP TABLE #OkUsers;

Temp tables work good with very large volume of data.Otherwise you can use a memory table.