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)

3 Table Join

I need to perform a 3 table join but my sql is a little rusty and I was never that good at Joins anyway!

I'll try and explain the basic DB layout. It's for a very simple forum board I'm making, I have 3 tables at the moment, one contains the messages which belong to a topics, which have their own table. And the topics all belong to a table listing all the forums.

tblThread
threadID PK
topicID FK
Author
Message
MessageDate

tblTopic
topicID PK
forumID FK
Subject
numViews

tblForum
forumID PK
forumName
forumDescription
forumOwner

What I'm trying to achieve is to get the number threads belonging to a forum.

So far my attempt at an sql query looks like this:

SELECT COUNT('tblthread.threadID') AS postsCount
FROM tblThread
INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1

but that's missing an operator somewhere. Anyone know where i'm going wrong?

Thanks,
RichardSELECT COUNT('tblthread.threadID') AS postsCount
There shouldn't be apostrophes around the column name.|||your statement seems be fine. what database server do you use? there might be some syntax problem (INNER JOIN or something) try this one, but basically that's the same:

SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1|||There shouldn't be apostrophes around the column name.
actually this shouldn't be issue. you can put whatever as count() argument. it can be column name, *, or some constant such as 1 or 'XXX'. in this case it's constant string which shouldn't affect result.|||True, I stand corrected.|||Hi Guys,
Thanks for the help. I'm using MS Access as the DB. I tried your suggestion madafaka but I'm still getting the same error:

"Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause."

Is this an error specific to access?

Thanks.|||daffy_dowden,
I created all 3 tables in MS Access (I think it's 2000 version)

I created Query (Create Query in Design view) using (SQL View)
simply: pasted code posted before

your code returned exactly the same error as you described. I don't know why, but what you can expect from MS Access :-)
Then I used (Design view) and it generated this code, which works fine:

SELECT count(*)
FROM tblForum
INNER JOIN (tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID) ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1;

code I posted before worked fine, so I don't know why you received an error

SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1;|||I guess Access joins tables step by step : tblThread join tblTopic, and then join tblForum

SELECT COUNT('tblthread.threadID') as postsCount
FROM
(tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID)
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1