Check for multiple rows using joins

Question

I am stuck while creating query for this. Below are the tables.

CREATE TABLE [dbo].[Mst_Conflict_Roles]
(
    [CRLm_ID] [dbo].[ITS_BID] IDENTITY(1,1) NOT NULL,
    [CRLm_Roles] [varchar](10) NOT NULL,
    [CRLm_Description] [varchar](200) NULL,
    [CRLm_Added_By] [dbo].[ITS_UserID] NOT NULL,
    [CRLm_Added_Dt] [dbo].[ITS_Datetime] NOT NULL,
    [CRLm_Last_Updated_By] [dbo].[ITS_UserID] NULL,
    [CRLm_Last_Updated_Dt] [dbo].[ITS_Datetime] NULL,
    [CRLm_data_del] [dbo].[ITS_TID] NOT NULL,

    CONSTRAINT [PK_Mst_Conflict_Roles] 
        PRIMARY KEY CLUSTERED ([CRLm_ID] ASC)
) ON [PRIMARY]

INSERT INTO [Mst_Conflict_Roles]
VALUES ('2,113', 'Scheduler - Auditor', 1, GETDATE(), 1, GETDATE(), 0),
       ('110,113','AE - Scheduler',1,GETDATE(),1,GETDATE(),0),
       ('2,121','Auditor - Vendor CAP User',1,GETDATE(),1,GETDATE(),0),
       ('8,9','AM - AC',1,GETDATE(),1,GETDATE(),0)



declare @userRole as table(userID int, userRole int)
Insert into @userRole values (1, 2),(1, 113),(1, 8),(1, 9),(1, 50),(2, 110),(2, 50),(3, 2),(3, 121)

select * from @userRole

Logic : If user role(userRole) match the conflict table roles(CRLm_Roles) then record must be appear in output table.

Output

UserID  CRLm_ID CRLm_description
1          1       2,113
1          4       8,9
3          3       2,121

Can anyone tell me how I can approach this?

Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Sep 11 '17 at 10:12
@ZoharPeled : Yes, but we can split comma separated values to different rows and then match using join. This is where i am facing issue as if user has both roles which i have in two different rows. — Paresh J, Sep 11 '17 at 10:26

Yared · Accepted Answer · 2017-09-11T15:12:15.083

The following query returns the desired output:

WITH    tmp ( CRLm_ID, CRLm_Roles, Role_ID, Data )
      AS ( SELECT   CRLm_ID ,
                    CRLm_Roles ,
                    CONVERT(NVARCHAR, LEFT(CRLm_Roles,
                                           CHARINDEX(',', CRLm_Roles + ',')
                                           - 1)) ,
                    STUFF(CRLm_Roles, 1, CHARINDEX(',', CRLm_Roles + ','),
                          '')
           FROM     [dbo].[Mst_Conflict_Roles]
           UNION ALL
           SELECT   CRLm_ID ,
                    CRLm_Roles ,
                    CONVERT(NVARCHAR, LEFT(Data,
                                           CHARINDEX(',', Data + ',') - 1)) ,
                    STUFF(Data, 1, CHARINDEX(',', Data + ','), '')
           FROM     tmp
           WHERE    Data > ''
         )
SELECT  UserID, CRLm_ID, CRLm_Roles AS CRLm_description
FROM    ( SELECT    CRLm_ID ,
                    CRLm_Roles ,
                    Role_ID ,
                    userRole.* ,
                    R = ROW_NUMBER() OVER ( PARTITION BY userID, CRLm_ID ORDER BY userID )
          FROM      tmp
                    JOIN userRole ON tmp.Role_ID = userRole.userRole
        ) x
WHERE   R = 2
OPTION (maxrecursion 0);

Output:

Check for multiple rows using joins

1 Answers1