Wow it still pretty tough to figure out what you want as output. I took the liberty of extracting your ddl and sample data here so others can find it without having to open a word doc.

CREATE TABLE [dbo].[USER_PRFL](

[User_Id] [varchar](30) NOT NULL,

[User_FullName] [varchar](50) NULL,

[Email_Address] [varchar](30) NULL,

[User_Group] [varchar](30) NULL,

CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED

(

[User_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

insert into USER_PRFL values('Mike', 'Michael Vaughan', 'mike@ex.com','CCSG Group');

insert into USER_PRFL values('Adams', 'Paul Adams', 'adams@ex.com','Service Group');

insert into USER_PRFL values('Jim', 'Jimmy Corner', 'jim@ex.com','LOB Group');

insert into USER_PRFL values('Kate', 'Kate Larry', 'kate@ex.com','Site Group');

insert into USER_PRFL values('Russell', 'Russell Westbrook', 'russell@ex.com','Sales Group');

CREATE TABLE [dbo].[BSP_LOB_Grp_Lookup](

[BSP_LOB_GRP] [varchar](20) NOT NULL,

[BSP_LOB_CD] [char](3) NULL,

[BSP_LOB_GRP_TYPE_CD] [char](3) NULL

) ON [PRIMARY]

GO

insert into BSP_LOB_Grp_Lookup values ('CCSG Group', 'CG','CGP')

insert into BSP_LOB_Grp_Lookup values ('Service Group', 'SG','SGP')

insert into BSP_LOB_Grp_Lookup values ('LOB Group', 'LG','LGP')

insert into BSP_LOB_Grp_Lookup values ('Site Group', 'SIG','LGP')

insert into BSP_LOB_Grp_Lookup values ('Sales Group', 'SAG','LGP')

CREATE TABLE [dbo].[IMPACTED_LOB](

[Event_ID] [int] NOT NULL,

[BSP_LOB_CD] [char](3) NOT NULL,

CONSTRAINT [PK_IMPACTED_LOB] PRIMARY KEY CLUSTERED

(

[Event_ID] ASC,

[BSP_LOB_CD] 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

insert into IMPACTED_LOB values('1', 'CG')

insert into IMPACTED_LOB values('2', 'SG')

insert into IMPACTED_LOB values('3', 'SG')

insert into IMPACTED_LOB values('4', 'LG')

insert into IMPACTED_LOB values('5', 'CG')

insert into IMPACTED_LOB values('6', 'LG')

insert into IMPACTED_LOB values('7', 'SG')

insert into IMPACTED_LOB values('8', 'LG')

insert into IMPACTED_LOB values('9', 'CG')

insert into IMPACTED_LOB values('10', 'SIG')

insert into IMPACTED_LOB values('11', 'SAG')

insert into IMPACTED_LOB values('12', 'SAG')

insert into IMPACTED_LOB values('13', 'SIG')

As I stated I really don't know what you want for output but something like this might get you started?

select *

from USER_PRFL p

join BSP_LOB_Grp_Lookup gl on gl.BSP_LOB_GRP = p.User_Group

join IMPACTED_LOB l on l.BSP_LOB_CD = gl.BSP_LOB_CD