Query multilayer table in sql

Question

I am trying to retrieve the UserInfo with the UserId=10004 with all his friends and all the posts of his friends and all the likes on those posts. And for that I am using a query :

select *,
    (select * ,
        (select * ,
            (select * from PostLikes where PostId=UserPosts.PostId) as likes
        from UserPosts where UserId=FriendsRelation.PersonId1 or UserId=FriendsRelation.PersonId2) as posts
    from FriendsRelation where PersonId1=UserId or PersonId2=UserId) as friends
from UserInfo 
where UserId=10004

but it is returning with an error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

How can I solve it?

Here are the tables that I am using:

CREATE TABLE [dbo].[UserInfo]
(
    [UserId] [bigint] IDENTITY(1,1) NOT NULL,
    [Username] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NOT NULL,
    [UserPassword] [nvarchar](max) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Gender] [nchar](10) NOT NULL,
    [ContactNo] [bigint] NOT NULL,
    [DateOfBirth] [date] NOT NULL,
    [RelationshipStatus] [nchar](10) NULL,
    [InterestedIn] [nchar](10) NULL,
    [Address] [nvarchar](max) NULL,
    [Country] [nchar](10) NOT NULL,
    [FavouriteQuote] [nvarchar](max) NULL,
    [DisplayPhoto] [nvarchar](max) NULL,
    [Guid] [nvarchar](max) NOT NULL,
    [Status] [tinyint] NOT NULL CONSTRAINT [DF_UserInfo_Status]  DEFAULT ((1)),
    [CreatedDate] [datetime] NOT NULL,
    [LastLogIn] [datetime] NULL,

    CONSTRAINT [PK_UserInfo] 
    PRIMARY KEY CLUSTERED ([UserId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

UserPosts table:

CREATE TABLE [dbo].[UserPosts]
(
    [PostId] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NOT NULL,
    [PostText] [nvarchar](max) NULL,
    [PostPicture] [nvarchar](max) NULL,
    [Time] [time](7) NOT NULL,
    [Date] [date] NOT NULL,
    [LikeCount] [int] NULL CONSTRAINT [DF_UserPosts_LikeCount]  DEFAULT ((0)),

    CONSTRAINT [PK_UserPosts] 
    PRIMARY KEY CLUSTERED ([PostId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserPosts]  WITH CHECK 
  ADD CONSTRAINT [FK_UserPosts_UserInfo] 
  FOREIGN KEY([UserId]) REFERENCES [dbo].[UserInfo] ([UserId])
     ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserPosts] CHECK CONSTRAINT [FK_UserPosts_UserInfo]
GO

and PostLikes table:

CREATE TABLE [dbo].[PostLikes]
(
    [LikeId] [bigint] IDENTITY(1,1) NOT NULL,
    [PostId] [bigint] NOT NULL,
    [UserId] [bigint] NOT NULL,

    CONSTRAINT [PK_PostLike] 
    PRIMARY KEY CLUSTERED ([PostId] ASC, [UserId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PostLikes]  WITH CHECK 
  ADD CONSTRAINT [FK_PostLikes_UserInfo] 
    FOREIGN KEY([UserId]) REFERENCES [dbo].[UserInfo] ([UserId])
GO

ALTER TABLE [dbo].[PostLikes] CHECK CONSTRAINT [FK_PostLikes_UserInfo]
GO

ALTER TABLE [dbo].[PostLikes]  WITH CHECK 
  ADD CONSTRAINT [FK_PostLikes_UserPosts] 
    FOREIGN KEY([PostId]) REFERENCES [dbo].[UserPosts] ([PostId])
       ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PostLikes] CHECK CONSTRAINT [FK_PostLikes_UserPosts]
GO

Answer 1

Similar to other people's answers, but use left joins for everything (in case the user has no friends), and some changes to join logic.

declare @userID int = 10004 --For easy swapping out if you want to query someone else
Select * from UserInfo a
left join FriendsRelation b
    on b.PersonID1 = @userID or b.PersonID2 = @userID --Faster than joining and then filtering, if you're only looking for one person at a time
left join UserPosts c
    on (c.UserID = b.PersonID1 or c.UserID = b.PersonID2) 
    and c.UserID <> @userID --returns only friends' posts, not user's posts, as specified in original question
left join postLikes d
    on d.PostID = c.PostID
where a.UserID = @userID