Append tables to create unique dataset

markej
markej used Ask the Experts™
on
I have a stored procedure that includes the TWO select statements shown below, I need to append the data into a single temporary table, removing all duplicates (the unique key is email) HELP how can I do this? I've tried creating a temporary table and using "select into" BUT creates an error in the stored procedure temporary table already exists .


Mark

		SELECT DISTINCT dbo.Userinfo.id, dbo.Userinfo.Email, dbo.Userinfo.fname, dbo.Userinfo.lname, dbo.Userinfo.status, dbo.Userinfo.parentid, dbo.Userinfo.usertype 
FROM            dbo.Userinfo LEFT OUTER JOIN
                         dbo.groupInfo ON dbo.Userinfo.ClubId = dbo.groupInfo.clubid AND dbo.Userinfo.id = dbo.groupInfo.userinfo_id LEFT OUTER JOIN
                         dbo.TeamMembers ON dbo.Userinfo.id = dbo.TeamMembers.userinfo_id LEFT OUTER JOIN
                         dbo.UserInfo_Static_Roles ON dbo.Userinfo.id = dbo.UserInfo_Static_Roles.userinfo_Id
		WHERE      
		dbo.Userinfo.status <> 3 and (dbo.TeamMembers.status is null or dbo.TeamMembers.status <> 3 ) and 
		(dbo.Userinfo.ClubId = @clubid) AND (@roles IS NULL OR dbo.UserInfo_Static_Roles.Static_Roles_id in (select VALUE from dbo.SPLIT( @roles)) )
		AND (@status IS NULL OR dbo.Userinfo.status = @status) 
		AND (@teamid IS NULL OR dbo.TeamMembers.Teamid = @teamid) 
		AND (@Groupid is NULL OR dbo.groupinfo.groupid = @Groupid)
		AND (@name is NULL OR dbo.Userinfo.lname LIKE @name OR dbo.Userinfo.fname LIKE @name OR dbo.Userinfo.Email LIKE @name)
		ORDER BY dbo.Userinfo.lname, dbo.Userinfo.fname


		SELECT DISTINCT dbo.Userinfo.id, dbo.Userinfo.Email, dbo.Userinfo.fname, dbo.Userinfo.lname, dbo.Userinfo.status, dbo.Userinfo.parentid, dbo.Userinfo.usertype
FROM            dbo.Userinfo INNER JOIN
                         dbo.LnkParent_ChildIDs ON dbo.Userinfo.id = dbo.LnkParent_ChildIDs.ParentUserinfoId INNER JOIN
                         dbo.TeamMembers ON dbo.LnkParent_ChildIDs.ChildUserinfoId = dbo.TeamMembers.userinfo_id LEFT OUTER JOIN
                         dbo.groupInfo ON dbo.Userinfo.ClubId = dbo.groupInfo.clubid AND dbo.Userinfo.id = dbo.groupInfo.userinfo_id LEFT OUTER JOIN
                         dbo.UserInfo_Static_Roles ON dbo.Userinfo.id = dbo.UserInfo_Static_Roles.userinfo_Id
		WHERE      
		dbo.Userinfo.status <> 3 and (dbo.TeamMembers.status is null or dbo.TeamMembers.status <> 3 ) and 
		(dbo.Userinfo.ClubId = @CLUBID) 
		AND (@roles IS NULL OR dbo.UserInfo_Static_Roles.Static_Roles_id in (select VALUE from dbo.SPLIT( @roles)) )
		AND (@status IS NULL OR dbo.Userinfo.status = @status) 
		AND (@teamid IS NULL OR dbo.TeamMembers.Teamid = @teamid) 
		AND (@Groupid is NULL OR dbo.groupinfo.groupid = @Groupid)
		AND (@name is NULL OR dbo.Userinfo.lname LIKE @name OR dbo.Userinfo.fname LIKE @name OR dbo.Userinfo.Email LIKE @name)
		ORDER BY dbo.Userinfo.lname, dbo.Userinfo.fname

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Senior Developer
Commented:
Step 1: Don't use VALUE as object name, it's a reserved word.
Step 2: Use table alias names.
Step 3: Use the UNION clause.
Step 4: "Remove" duplicates (you haven't specified a criteria for duplicates).

E.g.

WITH Data1 AS
   (
       SELECT           UI.id,
                        UI.Email,
                        UI.fname,
                        UI.lname,
                        UI.status,
                        UI.parentid,
                        UI.usertype
       FROM             dbo.Userinfo UI
           LEFT JOIN    dbo.groupInfo GI ON UI.ClubId = GI.clubid
                                            AND UI.id = GI.userinfo_id
           LEFT JOIN    dbo.TeamMembers TM ON UI.id = TM.userinfo_id
           LEFT JOIN    dbo.UserInfo_Static_Roles UISR ON UI.id = UISR.userinfo_Id
       WHERE            UI.status <> 3
                        AND (   TM.status IS NULL
                                OR TM.status <> 3 )
                        AND ( UI.ClubId = @clubid )
                        AND (   @roles IS NULL
                                OR UISR.Static_Roles_id IN (   SELECT   [Value]
                                                               FROM     dbo.SPLIT(@roles) ))
                        AND (   @status IS NULL
                                OR UI.STATUS = @status )
                        AND (   @teamid IS NULL
                                OR TM.Teamid = @teamid )
                        AND (   @Groupid IS NULL
                                OR GI.groupid = @Groupid )
                        AND (   @name IS NULL
                                OR UI.lname LIKE @name
                                OR UI.fname LIKE @name
                                OR UI.Email LIKE @name )
   ),
     Data2 AS
   (
       SELECT           UI.id,
                        UI.Email,
                        UI.fname,
                        UI.lname,
                        UI.status,
                        UI.parentid,
                        UI.usertype
       FROM             dbo.Userinfo UI
           INNER JOIN   dbo.LnkParent_ChildIDs ON UI.id = dbo.LnkParent_ChildIDs.ParentUserinfoId
           INNER JOIN   dbo.TeamMembers TM ON dbo.LnkParent_ChildIDs.ChildUserinfoId = TM.userinfo_id
           LEFT JOIN    dbo.groupInfo GI ON UI.ClubId = GI.clubid
                                            AND UI.id = GI.userinfo_id
           LEFT JOIN    dbo.UserInfo_Static_Roles UISR ON UI.id = UISR.userinfo_Id
       WHERE            UI.status <> 3
                        AND (   TM.status IS NULL
                                OR TM.status <> 3 )
                        AND ( UI.ClubId = @CLUBID )
                        AND (   @roles IS NULL
                                OR UISR.Static_Roles_id IN (   SELECT   [Value]
                                                               FROM     dbo.SPLIT(@roles) ))
                        AND (   @status IS NULL
                                OR UI.STATUS = @status )
                        AND (   @teamid IS NULL
                                OR TM.Teamid = @teamid )
                        AND (   @Groupid IS NULL
                                OR GI.groupid = @Groupid )
                        AND (   @name IS NULL
                                OR UI.lname LIKE @name
                                OR UI.fname LIKE @name
                                OR UI.Email LIKE @name )
   ),
     Unioned AS
   (
       SELECT   D.id,
                D.Email,
                D.fname,
                D.lname,
                D.status,
                D.parentid,
                D.usertype
       FROM     Data1 D
       UNION
       SELECT   D.id,
                D.Email,
                D.fname,
                D.lname,
                D.status,
                D.parentid,
                D.usertype
       FROM     Data2 D
   ),
     Ordered AS
   (
       SELECT   U.id,
                U.Email,
                U.fname,
                U.lname,
                U.status,
                U.parentid,
                U.usertype,
                ROW_NUMBER() OVER ( PARTITION BY U.Email
                                    ORDER BY U.fname,
                                             U.lname ) AS RN
       FROM     Unioned U
   )
SELECT  O.id,
        O.Email,
        O.fname,
        O.lname,
        O.status,
        O.parentid,
        O.usertype
FROM    Ordered O
WHERE   O.RN = 1;

Open in new window

No need for a temporary table.
markej

Author

Commented:
Thanks, That's seems to almost work. The id column is the primary key so I have replaced the U.Email with U.Id and that returns the correct records without any duplicates.


I'm trying to understand how this works so where would be a good place to read up on this?


Mark

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial