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)
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
EmoticonEmoticon