locked
Table merging RRS feed

  • Question

  • I have three tables to be merged as one they are, USER ACCOUNT TABLE, USER POST TABLE, USER FOLLOWERS TABLE

    The tables will be merged in a way that user A details will be displaed along side with posts made by users following user A, same goes to all users. but my procedure code is not getting it right, is displaying every post and users on the database.

    here is procedure for User posts connected to other users

    USE [conn]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[GetUSERPost]
    	@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 FOLLOWERS TABLE

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[USERFollow](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[MyUserName] [nvarchar](500) NULL,
    	[FriendUserName] [nvarchar](500) NULL,
    	[USERStatus] [bit] NULL,
    	[Status] [nvarchar](50) NULL,
    	[SendDate] [datetime] NULL,
     CONSTRAINT [PK_USERFollow] 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].[USERFollow] ADD  CONSTRAINT [DF_USERFollow_USERStatus]  DEFAULT ((0)) FOR [USERStatus]
    GO
    
    ALTER TABLE [dbo].[USERFollow] ADD  CONSTRAINT [DF_USERFollow_Status]  DEFAULT ('Following') FOR [Status]
    GO
    
    ALTER TABLE [dbo].[USERFollow] ADD  CONSTRAINT [DF_USERFollow_SendDate]  DEFAULT (getdate()) FOR [SendDate]
    GO


    USER POST TABLE

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[USERPost](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[MyUserName] [nvarchar](500) NULL,
    	[FriendUserName] [nvarchar](500) NULL,
    	[Message] [nvarchar](max) NULL,
    	[MessageStatus] [nvarchar](50) NULL,
    	[Count] [int] NULL,
    	[SendDate] [datetime] NULL,
    	[ImageName1] [nvarchar](500) NULL,
     CONSTRAINT [PK_USERPost] 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_USERPost_SendDate]  DEFAULT (getdate()) FOR [SendDate]
    GO


    USER ACCOUNT TABLE

    SET QUOTE

    D_IDENTIFIER ON GO CREATE TABLE [dbo].[User3]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](500) NOT NULL, [Email] [nvarchar](500) NOT NULL, [UserName] [nvarchar](500) NOT NULL, [Password] [nvarchar](500) NOT NULL, [Gender] [nvarchar](500) NOT NULL, [MaritalStatus] [nvarchar](500) NOT NULL, [BirthDay] [date] NOT NULL, [Profession] [nvarchar](500) NULL, [State] [nvarchar](500) NOT NULL, [Country] [nvarchar](500) NULL, [ImageName] [nvarchar](505) 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 ACCOUNT


    USER ACCOUNTUSER POSTmicah D

    USER FOLLOW WITH DATA

    USER FOLLOW TABLE WITH DATA


    • Edited by migold Friday, February 12, 2016 6:54 AM
    Friday, February 12, 2016 6:29 AM

Answers

All replies

  • Can you post sample data  along with desired result?

    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 
    JOIN CommunityPost as s ON.....,
    JOIN CommunityInfo j ON .....,
    JOIN  CommunityFollow z  ON ......
    WHERE s.MyUserName= @UserName ORDER BY SendDate DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Friday, February 12, 2016 6:36 AM
  • the desired result is like , USER A is following USER B and C. USER A should be getting the posts made by USER B and C , While USER C and B will be getting post of USER A. But USER C wont see posts made by USER B unless they are following each other.

    micah D

    Friday, February 12, 2016 6:45 AM
  • So what is a desired result, I am confused so many pictures without explanation...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, February 12, 2016 6:56 AM
  • Looks like all you need is a simple join like this

    SELECT *
    FROM UserFollow f
    LEFT JOIN UserPosts p
    ON p.MyUserName = f.Username
    LEFT JOIN User3 u
    ON u.MyUserName = f.MyUserName

    If this is not what you expect post expected output for the above sample data


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, February 12, 2016 7:21 AM
  • Yes but am still not getting it right with your example. But let me explain more to you. 

    THIS IS THE IMAGE OF USER ACCOUNT TABLE BELOW

    USER ACCOUNT

    THIS IS THE IMAGE OF USER POSTS TABLE

    USER POST

    THIS IS THE USER TO USER FOLLOW

    USER TO USER FOLLOW

    FINALLY THIS IS THE USER GROUP ACCOUNT THAT HOLDS ALL THE INFORMATION OF THE GROUP WHICH EACH THE USER BLONGED. EXAMPLE IT COULD BE FEMALE EQULITY GENDER GROUP. 

    GROUP DETAILS

    Now lets say we have 5 users on the table User account table, USER 1, USER 2, USER 3, USER 4 and USER 5. So USER 1 created group account called Female equality group. If USER 2 and USER 3 is following USER 1 ,that means that USER 2 and USER 3 will be seeing posts made by USER 1 and same goes to USER 1 , the three users USER 1, USER 2 and USER 3 will be seeing each others post because the are following themselves. But USER 4 and USER 5 wont be seeing posts made by USER 1, USER 2 , USER 3 because they are not following them in that group

    This is the table of USER ACCOUNT

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[User3](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [nvarchar](500) NOT NULL,
    	[Email] [nvarchar](500) NOT NULL,
    	[UserName] [nvarchar](500) NOT NULL,
    	[Password] [nvarchar](500) NOT NULL,
    	[Gender] [nvarchar](500) NOT NULL,
    	[MaritalStatus] [nvarchar](500) NOT NULL,
    	[BirthDay] [date] NOT NULL,
    	[Profession] [nvarchar](500) NULL,
    	[State] [nvarchar](500) NOT NULL,
    	[Country] [nvarchar](500) NULL,
    	[ImageName] [nvarchar](505) 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


    THIS IS THE TABLE FOR USER POSTS

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[CommunityPost](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[MyUserName] [nvarchar](500) NULL,
    	[FriendUserName] [nvarchar](500) NULL,
    	[Message] [nvarchar](max) NULL,
    	[MessageStatus] [nvarchar](50) NULL,
    	[Count] [int] NULL,
    	[SendDate] [datetime] NULL,
    	[ImageName1] [nvarchar](500) 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].[CommunityPost] ADD  CONSTRAINT [DF_CommunityPost_SendDate]  DEFAULT (getdate()) FOR [SendDate]
    GO


    THIS IS THE USER GROUP INFO ACCOUNT THAT HOLDS THE ACCOUNT OF THAT GROUP I MENTIONED ABOVE

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[CommunityInfo](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[MyId] [int] NULL,
    	[UserName] [nvarchar](500) NULL,
    	[CommunityName] [nvarchar](500) NULL,
    	[Interest] [nvarchar](50) NULL,
    	[AboutUs] [nvarchar](max) NULL,
    	[Email] [varchar](50) NULL,
    	[Address] [nvarchar](50) NULL,
    	[Phone] [nvarchar](50) NULL,
    	[RegisterDate] [datetime] NULL,
    	[Services] [nvarchar](50) NULL,
    	[Country] [varchar](30) NULL,
    	[ImageBanner] [varchar](200) NOT NULL,
    	[Logo] [varchar](200) NULL,
     CONSTRAINT [PK_CommunityInfo] 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
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[CommunityInfo] ADD  CONSTRAINT [DF_CommunityInfo_RegisterDate]  DEFAULT (getdate()) FOR [RegisterDate]
    GO

    THIS IS THE TABLE FOR USER TO USER FOLLOW

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[CommunityFollow](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[MyUserName] [nvarchar](500) NULL,
    	[FriendUserName] [nvarchar](500) NULL,
    	[CommunityStatus] [bit] NULL,
    	[Status] [nvarchar](50) 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

    So in summery, once a user is following another user in a group both should be seeing their posts, while those who are not following will not. On the USER FOLLOW table followers are directly opposite the other User. Example KenTal is following Micah22 while Kaynt6 is following ClassLady. Please Note you may not follow this method since i dont know if this method is the right approach.


    micah D



    • Edited by migold Friday, February 12, 2016 11:00 AM
    Friday, February 12, 2016 10:23 AM