3 table joins - 3rd table join main not exist (return null columns) - please help!

Hello SQL Guru's,

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 join

hope 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)