Help with a nested query !!

Question

        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 ..

Answer 1

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)

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 WHERE a.SenderId = @id OR a.ReceiverId = @id;

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;

Greets Flo

Answer 2

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.

Answer 3

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;