Add sub category to joined tables in Procedure in SQL Server

micah

SOLVED CLOSED ACTIVE ANSWERED
User: micah
Posted: on Jul 10, 2017 02:35 AM
Forum: SQL Server
Answer: 1
Views: 5467

I have this procedure below that helps those in category A Admin to see their posts , with the query below if Admin Sky and Admin Blue is connected to each other in Category A group admin they will be seeing each others post.

A CATEGORY --------------------------

ALTER PROC [dbo].[GetUserPOSTS]
    @UserName VARCHAR (200)
AS
BEGIN


   SELECT profile.Name,profile.ImageName,profile.UserName,profile.UserVerifyStatus,
           post.Id,post.UserName,post.FriendUserName,post.ContentPost,post.Comments,post.FImageName,post.ImageName1,post.SendDate,
post.TotalCount,post.Path
		   
    FROM USERPost post
    INNER JOIN User3 profile ON profile.UserName = post.USERNAME,subb.USERNAME
	
    WHERE  post.USERNAME  IN (
	                    SELECT uf.UserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                        UNION
                        SELECT uf.FriendUserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
						  UNION
                       
                        SELECT post.UserName FROM User3 post
                        WHERE post.UserName = @UserName 
						  
						
                      
                         )
                      
   
			
						
    ORDER BY post.Id DESC
	END

CATEGORY B SUB --------------------------------------------------

Now here is SUB catgory B Adim account here admins who are connected together will be seeing each others post

ALTER PROC [dbo].[GetUserPOSTSSUB]
    @UserName VARCHAR (200)
AS
BEGIN


   SELECT profilesub.Name,profilesub.ImageName,profilesub.UserName,profilesub.UserVerifyStatus,
          subpost.UserName,subpost.ContentPost
		   
    FROM SUBGROUPSpost subpost
    INNER JOIN User3 profilesub ON profilesub.UserName = subpost.USERNAME
	
    WHERE  subpost.USERNAME  IN (
	                   
                        SELECT sub.FriendUserName FROM Subgroups sub
                        WHERE sub.UserName = @UserName AND sub.UserStatus = 'true'
                         UNION
                        SELECT subpost.UserName FROM User3 subpost
                        WHERE subpost.UserName = @UserName 
						  
						
                      
                         )
                      
   
			
						
    ORDER BY subpost.Id DESC
	END


-----------------------------------

3rd stage join all Here i want to bring the two scripts on one procedure script so that the posts from the two categories can appear on one Datalist on page

ALTER PROC [dbo].[GetUserPOSTS]
    @UserName VARCHAR (200)
AS
BEGIN


   SELECT profile.Name,profile.ImageName,profile.UserName,profile.UserVerifyStatus,

profilesub.Name,profilesub.ImageName,profilesub.UserName,profilesub.UserVerifyStatus,
          subpost.UserName,subpost.ContentPost

           post.Id,post.UserName,post.FriendUserName,post.ContentPost,post.Comments,post.FImageName,post.ImageName1,post.SendDate,
post.TotalCount,post.Path
		   
    FROM USERPost post
    INNER JOIN User3 profile ON profile.UserName = post.USERNAME,subpost.USERNAME
	
    WHERE  post.USERNAME  IN (
	                    SELECT uf.UserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                        UNION
                        SELECT uf.FriendUserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
						  UNION
                       
                        SELECT post.UserName FROM User3 post
                        WHERE post.UserName = @UserName 
						  
						
                      
                         )
                      
   
			
						
    ORDER BY post.Id DESC
	END

So far am stuck on this 3rd stage, any help

Indresh

0 1

Reply
User: Indresh
Replied: on Jul 10, 2017 09:28 AM
Report

Hi micah,

Will you please share your table structure with expected output.

micah

0 2

Reply
User: micah
Replied: on Jul 21, 2017 12:57 AM
Modified: on Jul 21, 2017 01:06 AM
Report

HERE

CREATE TABLE [dbo].[User3](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Email] [nvarchar](500) NULL,
	[UserName] [nvarchar](200) NULL,
	[Password] [nvarchar](500) NOT NULL,
	[BirthDay] [date] NULL,
	[Country] [nvarchar](500) NULL,
	[ImageName] [nvarchar](505) NULL,
	
	[RegisterdDate] [date] NULL,
	[UserVerifyStatus] [nvarchar](505) NULL,



----------------------------------------------------------------

CREATE TABLE [dbo].[USERPost](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NULL,
	[FriendUserName] [varchar](50) NULL,
	[fName] [nvarchar](500) NULL,
	[Comments] [nvarchar](max) NULL,
	[FImageName] [nvarchar](500) NULL,
----------------------------------------------------------
CREATE TABLE [dbo].[GetUserPOSTSSUB](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NULL,
	[FriendUserName] [varchar](50) NULL,
	[fName] [nvarchar](500) NULL,
	[Comments] [nvarchar](max) NULL,
	[FImageName] [nvarchar](500) NULL,
--------------------------------------------------------------------
CREATE TABLE [dbo].[FollowStatus](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NULL,
	[FriendUserName] [varchar](50) NULL,
	[UserStatus] [bit] NULL,
	[Count] [int] NULL,
 CONSTRAINT [PK_FollowStatus] PRIMARY KEY CLUSTERED 
-------------------------------------------------------------------------
DATALIST OUTCOME ON BOTH UserPOST Table AND UserPOSTB Table All Appear on same datalist by table id

1 from UserPOST Table    |    Username Max22   |  Contentpost hello world
---------------------------------------------------------------------------------
2 from UserPOSTB Table   |    usernme Mark66   |  Contentpost hi world
-------------------------------------------------------------------------------------
3 from UserPOST Table    |    Username INDKA55 |  Contentpost hello world
-----------------------------------------------------------------------------------
4 from UserPOSTB Table   |    Username Max22   |  Contentpost Nice coding
-----------------------------------------------------------------------

BOTH MAX22, MARK66 AND INDKA55 ARE CONNECTED ON UserFollow table, so the script helps them to see their post So the challange now is to bring table USERPOST AND USERPOSTB Tables on one procedure so that both can be controlled by one table ID making posts from both tables appear on one datalist like below

DATALIST OUTCOME ON BOTH UserPOST Table AND UserPOSTB Table All Appear on same datalist by table id

1 from UserPOST Table    |    Username Max22   |  Contentpost hello world
---------------------------------------------------------------------------------
2 from UserPOSTB Table   |    usernme Mark66   |  Contentpost hi world
-------------------------------------------------------------------------------------
3 from UserPOST Table    |    Username INDKA55 |  Contentpost hello world
-----------------------------------------------------------------------------------
4 from UserPOSTB Table   |    Username Max22   |  Contentpost Nice coding
-----------------------------------------------------------------------
ALTER PROC [dbo].[GetUserPOSTS]
    @UserName VARCHAR (200)
AS
BEGIN
 
 
   SELECT profile.Name,profile.ImageName,profile.UserName,profile.UserVerifyStatus,
 
profilesub.Name,profilesub.ImageName,profilesub.UserName,profilesub.UserVerifyStatus,
          subpost.UserName,subpost.ContentPost
 
           post.Id,post.UserName,post.FriendUserName,post.ContentPost,post.Comments,post.FImageName,post.ImageName1,post.SendDate,
post.TotalCount,post.Path
            
    FROM USERPost post
    INNER JOIN User3 profile ON profile.UserName = post.USERNAME,subpost.USERNAME
     
    WHERE  post.USERNAME  IN (
                        SELECT uf.UserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                        UNION
                        SELECT uf.FriendUserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                          UNION
                        
                        SELECT post.UserName FROM User3 post
                        WHERE post.UserName = @UserName
                           
                         
                       
                         )
                       
    //JOIN THE SECOND GetUserPOSTS TABLE HERE BOTH WILL BE CONTROLED BY THESE QUERY BELOW

 WHERE  post.USERNAME  IN (
                        SELECT uf.UserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                        UNION
                        SELECT uf.FriendUserName FROM FollowStatus uf
                        WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                          UNION
                        
                        SELECT post.UserName FROM User3 post
                        WHERE post.UserName = @UserName
                           
             
                         
    ORDER BY post.Id DESC
    END

Indresh

0 3

Reply
ANSWER
User: Indresh
Replied: on Jul 21, 2017 05:01 AM
Modified: on Jul 21, 2017 07:43 AM
Report

Hi micah,

Please refer the below sql store procedure. You need to first check your sp in your sql server after that implement in your code behind.

SQL

ALTER PROCEDURE [dbo].[GetUserPOSTS] 
    @UserName VARCHAR (200)
AS
BEGIN

	SELECT profile.Name,profile.ImageName,profile.UserName,profile.UserVerifyStatus,
		   post.Id,post.UserName,post.FriendUserName,post.ContentPost,post.Comments,
		   post.FImageName,post.ImageName1,post.SendDate,post.TotalCount,post.Path
	FROM USERPost post
		 INNER JOIN User3 profile ON profile.UserName = post.USERNAME
		 WHERE  post.USERNAME IN (
								SELECT uf.UserName FROM FollowStatus uf
								WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
								 UNION
								SELECT uf.FriendUserName FROM FollowStatus uf
								WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
								 UNION
								SELECT post.UserName FROM User3 post
								WHERE post.UserName = @UserName)
								 ORDER BY post.Id DESC
		UNION ALL                     
		SELECT profilesub.Name,profilesub.ImageName,profilesub.UserName,profilesub.UserVerifyStatus,
			   subpost.Id,subpost.UserName,subpost.FriendUserName,subpost.ContentPost,subpost.Comments,
			   subpost.FImageName,subpost.ImageName1,subpost.SendDate,subpost.TotalCount,subpost.Path
		FROM SUBGROUPSpost subpost
			 INNER JOIN User3 profilesub ON profilesub.UserName = subpost.USERNAME
			 WHERE subpost.USERNAME IN(SELECT sub.FriendUserName FROM Subgroups sub
								WHERE sub.UserName = @UserName AND sub.UserStatus = 'true'
								UNION
								SELECT subpost.UserName FROM User3 subpost
								WHERE subpost.UserName = @UserName)
			 ORDER BY subpost.Id DESC
		 
	END

micah

0 4

Reply
User: micah
Replied: on Jul 21, 2017 06:06 AM
Report

hi please did you run it, it showing error

Server Error in '/' Application.
Invalid object name 'subb.USERNAME'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'post.USERNAME'.

Source Error:


Line 384:                SqlDataAdapter sda = new SqlDataAdapter(cmd);
Line 385:                DataTable dt = new DataTable();
Line 386:                sda.Fill(dt);
Line 387:                ViewState["DataTable"] = dt;
Line 388:                GetMergedAll.DataSource = dt;

iammann

0 5

Reply
User: iammann
Replied: on Jul 21, 2017 06:14 AM
Report

Please refer code below

ALTER PROCEDURE [dbo].[GetUserPOSTS]
    @UserName VARCHAR (200)
AS
BEGIN
 
    SELECT profile.Name,profile.ImageName,profile.UserName,profile.UserVerifyStatus,
           post.Id,post.UserName,post.FriendUserName,post.ContentPost,post.Comments,
           post.FImageName,post.ImageName1,post.SendDate,post.TotalCount,post.Path
    FROM USERPost post
         INNER JOIN User3 profile ON profile.UserName = post.USERNAME,subpost.USERNAME
         WHERE  post.USERNAME IN (
                                SELECT uf.UserName FROM FollowStatus uf
                                WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                                 UNION
                                SELECT uf.FriendUserName FROM FollowStatus uf
                                WHERE uf.UserName = @UserName AND uf.UserStatus = 'true'
                                 UNION
                                SELECT post.UserName FROM User3 post
                                WHERE post.UserName = @UserName)
                                 ORDER BY post.Id DESC
        UNION ALL                    
        SELECT profilesub.Name,profilesub.ImageName,profilesub.UserName,profilesub.UserVerifyStatus,
               subpost.Id,subpost.UserName,subpost.FriendUserName,subpost.ContentPost,subpost.Comments,
               subpost.FImageName,subpost.ImageName1,subpost.SendDate,subpost.TotalCount,subpost.Path
        FROM SUBGROUPSpost subpost
             INNER JOIN User3 profilesub ON profilesub.UserName = subpost.USERNAME
             WHERE subpost.USERNAME IN(SELECT sub.FriendUserName FROM Subgroups sub
                                WHERE sub.UserName = @UserName AND sub.UserStatus = 'true'
                                UNION
                                SELECT subpost.UserName FROM User3 subpost
                                WHERE subpost.UserName = @UserName)
             ORDER BY subpost.Id DESC
          
    END

Add sub category to joined tables in Procedure in SQL Server

Company

Explore

Follow us

Contact Us