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


Solution: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;  


Solution:2

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;  


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »