Table merging

Transact-SQL
1

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

2

This will constraint the S and U tables to the same UserName, but the other tables have no criteria so every row from the S & U joined tables will match every row in J and every row in Z, so you will get LOTs of rows :frowning:

Add a criteria for how the J and Z tables should be joined to either S or U tables.

I would recommend that you use JOIN syntax rather than the WHERE clause (as you have done so far)

1 Like
3

Yes you are right, I have lots of rows repeating it's self. But Please can you demonstrate how to achieve your example. I still can't contract it based on your definition

4

Add to your WHERE clause extra test for which records in J should be included. You haven;t included any definition of [CommunityInfo] so I don't know what columns are available, but if for example there is a column in [CommunityInfo] called [MyUserName] you could add

WHERE s.MyUserName= u.UserName
      AND j.MyUserName= u.UserName

and similarly with the Z table [CommunityFollow] (which does appear to have a [MyUserName] column)