Trying to join three tables with no success

Question

Post reply

Trying to join three tables with no success

Larry-126181

SSC Enthusiast

Points: 148
More actions
December 18, 2009 at 8:48 pm

#218873

Hi All,
Having trouble with a select statement across 3 tables:
tbl_defaultCategories - contains a limited list of default categories
tbl_customCategories - allows user to create custom categories not in default categories table
tbl_categoriesRefTable - contains list of custom and default categories FK and universityId as FK
So, multiple universities can have multiple default and custom categories. Here is the query I have, but it returns an empty result set.
SELECT dCats.NAME, cCats.NAME, refTable.Id
FROM tbl_categoriesRefTable AS refTable
INNER JOIN tbl_defaultCategories AS dCats (if I comment out this inner join get correct result for bottom inner join)
ON dCats.defaultCategoryId = refTable.defaultCategoryId AND
dCats.universityId = '1c67b5f5-89a7-4a97-9f9e-13f7bb1c2b41'
INNER JOIN tbl_customCategories AS cCats (if I comment out this inner join get correct result for upper inner join)
ON cCats.customCategoryId = refTable.customCategoryId
AND cCats.universityId = '1c67b5f5-89a7-4a97-9f9e-13f7bb1c2b41'
but if both are included, I get no results

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18939 More actions · Answer 1

You may want to redesign your schema.

With your current schema, something like the following may work:

SELECT dCats.[NAME]

,refTable.[Id]

,'Default' AS CatType

FROM tbl_categoriesRefTable AS refTable

JOIN tbl_defaultCategories AS dCats

ON dCats.defaultCategoryId = refTable.defaultCategoryId

AND dCats.universityId = '1c67b5f5-89a7-4a97-9f9e-13f7bb1c2b41'

UNION ALL

SELECT cCats.[NAME]

,refTable.[Id]

,'Custom'

FROM tbl_categoriesRefTable AS refTable

JOIN tbl_customCategories AS cCats

ON cCats.customCategoryId = refTable.customCategoryId

AND cCats.universityId = '1c67b5f5-89a7-4a97-9f9e-13f7bb1c2b41'

Larry-126181 SSC Enthusiast Points: 148 More actions · Answer 2

Thanks so much! That worked perfectly. I'm still learning complex sql queries and had tried using a UNION early on but couldn't get that to work correctly. Obviously I didn't have the syntax quite right.

Thanks again.