i have four tables that i want to merge, table USER, table USERPOST, TABLE USERFOLLOWERS , I want to display posts made by users who are following each other, just like in social network. but my procedure below is not getting it.
USE [CONN]
GO
/****** Object: StoredProcedure [dbo].[GetUSERPost] Script Date: 2/11/2016 8:32:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCommunityPost]
@UserName NVARCHAR(100)
AS
BEGIN
SELECT u.Id as UserId,u.Name,u.ImageName,u.UserName,j.CommunityName,s.ImageName1,s.FriendUserName,s.MyUserName,s.Message,s.SendDate,s.ID FROM [User3] as u, CommunityPost as s, CommunityInfo j, CommunityFollow z WHERE s.MyUserName= u.UserName ORDER BY SendDate DESC
END
USER ACCOUNT TABLE
/****** Object: Table [dbo].[User3] Script Date: 2/12/2016 7:09:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] nvarchar NOT NULL,
[Email] nvarchar NOT NULL,
[UserName] nvarchar NOT NULL,
[Password] nvarchar NOT NULL,
[Gender] nvarchar NOT NULL,
[MaritalStatus] nvarchar NOT NULL,
[BirthDay] [date] NOT NULL,
[Profession] nvarchar NULL,
[State] nvarchar NOT NULL,
[Country] nvarchar NULL,
[ImageName] nvarchar NULL,
[RegisterdDate] [date] NULL,
[LastLogin] [datetime] NULL,
CONSTRAINT [PK_User3] PRIMARY KEY CLUSTERED
(
[ID] 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].[User3] ADD CONSTRAINT [DF_User3_RegisterdDate] DEFAULT (getdate()) FOR [RegisterdDate]
GO
USER FOLLOWER TABLE
/****** Object: Table [dbo].[USERFollow] Script Date: 2/12/2016 7:10:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CommunityFollow](
[Id] [int] IDENTITY(1,1) NOT NULL,
[MyUserName] nvarchar NULL,
[FriendUserName] nvarchar NULL,
[CommunityStatus] [bit] NULL,
[Status] nvarchar NULL,
[SendDate] [datetime] NULL,
CONSTRAINT [PK_CommunityFollow] PRIMARY KEY CLUSTERED
(
[Id] 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].[CommunityFollow] ADD CONSTRAINT [DF_CommunityFollow_CommunityStatus] DEFAULT ((0)) FOR [CommunityStatus]
GO
ALTER TABLE [dbo].[CommunityFollow] ADD CONSTRAINT [DF_CommunityFollow_Status] DEFAULT ('Following') FOR [Status]
GO
ALTER TABLE [dbo].[CommunityFollow] ADD CONSTRAINT [DF_CommunityFollow_SendDate] DEFAULT (getdate()) FOR [SendDate]
GO
USER POST TABLE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CommunityPost](
[Id] [int] IDENTITY(1,1) NOT NULL,
[MyUserName] nvarchar NULL,
[FriendUserName] nvarchar NULL,
[Message] nvarchar NULL,
[MessageStatus] nvarchar NULL,
[Count] [int] NULL,
[SendDate] [datetime] NULL,
[ImageName1] nvarchar NULL,
CONSTRAINT [PK_CommunityPost] PRIMARY KEY CLUSTERED
(
[Id] 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].[USERPost] ADD CONSTRAINT [DF_CommunityPost_SendDate] DEFAULT (getdate()) FOR [SendDate]
GO