This has had me stumped for about 8 hours already and I think I've gotten to a point where I'm trying the same things over and over again and they are just not working. Any help would be greatly appreciated!
My Table Structure:
Table 1) 'Modules'
ModuleID | ModuleName | isVisible
--
1 Test 1 True
2 Test 2 True
3 Test 3 False
4 Test 4 True
Table 2) 'ModuleUserTypes'
ID | ModuleID | UserType
1 1 1
2 1 2
3 2 1
4 3 1
5 4 1
6 4 2
Table 3) 'ModuleUserSettings'
ID | ModuleID | UserID | CustomTitle | BGColor
--
1 2 1 New Title2 Black
2 2 2 New Title2 White
3 3 1 New Title3 Orange
4 4 1 NewTitle4 Yellow
My Goal:
To be able to join the 3 tables 'Modules', 'ModuleUserTypes', and 'ModuleUserSettings' together and return 'ModuleID, ModuleName, CustomTitle, BGColor' for ALL Modules with UserType = 1 along with associated ModuleUserSettings IF the UserSetting exists, otherwise NULL for the columns.
My desired result set:
UserID = 1
UserType = 1
isVisible = True
ModuleID | ModuleName | CustomTitle | BGColor
--
1 Test1 NULL NULL
2 Test2 New Title2 White
4 Test4 New Title4 Yellow
I'm sure this type of query will be easy for someone out there, but rather hard for me!
Thanks for your efforts!
Execute the following query, to get your results :
select m.ModuleID,m.ModuleName,CustomTitle,BGColor
from Modules m left join ModuleUserTypes mut on m.ModuleID = mut.ModuleID
left join ModuleUserSettings mus on m.ModuleID = mus.ModuleID
where (UserID is null or UserID = 1)
and UserType=1
and IsVisible = 1
Assumption - IsVisible column is bit data type
otherwise - use -
and IsVisible = 'True'
Thanks
Naras.
|||you need to include your User table or you table that define the usertype of a user and you need to use left joinhope this helps
SELECT *
INTO #Modules
FROM (
SELECT 1 AS ModuleID
,'Test 1' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 2 AS ModuleID
,'Test 2' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 3 AS ModuleID
,'Test 3' AS ModuleName
, 'False' AS isVisible
UNION ALL
SELECT 4 AS ModuleID
,'Test 4' AS ModuleName
, 'True' AS isVisible
) Modules
SELECT *
INTO #ModuleUserTypes
FROM ( SELECT 1 AS [ID]
, 1 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 2 AS [ID]
, 1 AS ModuleID
, 2 AS UserType
UNION ALL
SELECT 3 AS [ID]
, 2 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 4 AS [ID]
, 3 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 5 AS [ID]
, 4 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 6 AS [ID]
, 4 AS ModuleID
, 2 AS UserType
) ModuleUserTypes
SELECT *
INTO #ModuleUserSettings
FROM (
SELECT 1 AS [ID]
, 2 AS ModuleID
, 1 AS UserID
, 'New Title2' AS CustomTitle
, 'White' AS BGColor
UNION ALL
SELECT 2 AS [ID]
, 2 AS ModuleID
, 2 AS UserID
, 'New Title2' AS CustomTitle
, 'Black' AS BGColor
UNION ALL
SELECT 3 AS [ID]
, 3 AS ModuleID
, 1 AS UserID
, 'New Title3' AS CustomTitle
, 'Orange' AS BGColor
UNION ALL
SELECT 4 AS [ID]
, 4 AS ModuleID
, 1 AS UserID
, 'New Title4' AS CustomTitle
, 'Yellow' AS BGColor
) ModuleUserSettings
SELECT *
INTO #Users
FROM (
SELECT 1 AS UserID
, 1 AS UserType
UNION ALL
SELECT 2 AS UserID
, 2 AS UserType
) Users
DECLARE @.UserType int
DECLARE @.UserID int
DECLARE @.isVisible varchar(5)
SET @.UserType = 1
SET @.UserID = 1
SET @.isVisible = 'True'
SELECT DISTINCT
m.ModuleID
, m.ModuleName
, mus.CustomTitle
, mus.BGColor
FROM #Modules m LEFT OUTER JOIN
#ModuleUserTypes mut ON m.ModuleID = mut.ModuleID LEFT OUTER JOIN
#ModuleUserSettings mus ON m.ModuleID = mus.ModuleID
AND mut.ModuleID = mus.ModuleID LEFT OUTER JOIN
#Users ut ON mut.UserType = ut.UserType
AND mus.UserID = ut.UserID
WHERE ISNULL(mut.UserType,@.UserType) = @.UserType
AND ISNULL(mus.UserID,@.UserID) = @.UserID
AND ISNULL(m.isVisible,@.isVisible) = @.isVisible
DROP TABLE #Modules
DROP TABLE #ModuleUserTypes
DROP TABLE #ModuleUserSettings
DROP TABLE #Users|||
select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolour
from Modules m
join moduleusertypes mut
on m.moduleid = mut.moduleid
and mut.usertype = 1
left join ModuleUserSettings mus
on mus.moduleid = mut.moduleid
and mus.userid = mut.usertype
where IsVisible = 1
Assuming userid in 'ModuleUserSettings' is equal to UserType in 'ModuleUserTypes'
Regards,
kwareol
|||Thanks Nara's for your reply. I tried a similar statement but it was not filtering correctly. It would work until I added the UserType=1 and isVisible=1 to the where clause.|||Kwareol,
Your statement took me in the right direction!
All I needed to add was the UserID filter.
This is the final statement that works exactly as I needed:
select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolor
from Modules m
join moduleusertypes mut
on m.moduleid = mut.moduleid
and mut.usertype = 1
left join ModuleUserSettings mus
on mus.moduleid = mut.moduleid
and (mus.userid = 1 or mus.userid is null)
where IsVisible = 1
Thank you and everybody so much for your time and efforts!! It's much much appreciated!
(I marked this post as the final answer. I'm not exactly sure how this forums works and if users get some sort of point ranking for posting correct answers. If so, I will change it to Kwareol for him leading me in the direction I needed to go)
