Need help in solving SQL

Question

I am new in Asp.net now i want to try to start a social network project

I have 3 tables

User

Userid -Username -Email -Password -Name -Profilepic

Post

-Postid -Content -Postdate -Userid

Friendlist

Friendlistid - userid - friendid -status

i want to load the post that from my friend and myself

This is my sql code

select b.userid, a.Friendid, c.name, b.postid, b.Content, b.postdate, c.profilepic
from friendlist a
left join [user] c on a.friendid = c.userid
left join post b on a.friendid = b.userid
where a.userid = 1 AND a.Status = 1 OR a.Userid = 1
ORDER BY postid desc

The problem that i faced was my post will repeat

For example, my friendlist table has 3 friends, my every single post will repeat 3 times and my friend post stay normal.

Any solution for this sql code?

**Do not store passwords in plain text**. – SLaks Feb 22 '16 at 17:45 — SLaks, Feb 22 '16 at 17:45

score 0 · Answer 1 · edited May 23 '17 at 12:15

If you have a relationship between the tables and are using Entity Framework. You can simply invoke the tables below its "friendlist". You can take this query return creating a class specific to this return

public class ListFriendsGroup   
{      
        public int UserId { get; set; }
        public int friendId  { get; set; }
        public int PostId{ get; set; }
        public string Name { get; set; }
        public string Content { get; set; }
        public DataTime PostData { get; set; }
        public string ProfilePic { get; set; }
}

var friendlist = Context.friendList.Include("user").Include("post")
// HERE .GroupBy(... 
.Select(f => new ListFriendsGroup() { Userid = f.post.userid, FriendId = f.Friendid, Name = f.user.name, PostId = f.post.postid, Content = f.post.Content, PostData = f.post.postdate, Profilepic = f.user.profilepic }).ToList()

Read about the Group By clause to perform grouping of data

Group by, Count and Lambda Expression

Need help in solving SQL

1 Answers1