G
Guest
I need to join 4 tables R, S, C and X. I need an equi join between R and X
and a left join between R and S and R and C. None of my many SQL books give
the syntax for this. Anyone know the SQL syntax?
I have something that partially works but when the join column in table C is
null I get a row in the result set for every row in C instead of just the
null row.
Here is what I have right now (which does not use left joins):
SELECT R.ID, R.Title & "." & R.Chapter & "." & R.Article & "." & R.Topic &
iif(NZ(R.SubTopic,0)=0," ","." & R.SubTopic) AS RuleNumb, R.RuleName,
C.Category, S.Status
FROM tblRules AS R, tblPkgRuleXref AS X, tblCategories AS C, tblRuleStatuses
AS S
WHERE R.ID = X.RuleID and X.PkgID = [Forms]![frmPackages]![PkgID] AND
(C.ID = R.CategoryID OR R.CategoryID IS NULL) AND
(S.ID = R.StatusID OR R.StatusID IS NULL)
ORDER BY R.Title, R.Chapter, R.Article, R.Topic, R.SubTopic;
and a left join between R and S and R and C. None of my many SQL books give
the syntax for this. Anyone know the SQL syntax?
I have something that partially works but when the join column in table C is
null I get a row in the result set for every row in C instead of just the
null row.
Here is what I have right now (which does not use left joins):
SELECT R.ID, R.Title & "." & R.Chapter & "." & R.Article & "." & R.Topic &
iif(NZ(R.SubTopic,0)=0," ","." & R.SubTopic) AS RuleNumb, R.RuleName,
C.Category, S.Status
FROM tblRules AS R, tblPkgRuleXref AS X, tblCategories AS C, tblRuleStatuses
AS S
WHERE R.ID = X.RuleID and X.PkgID = [Forms]![frmPackages]![PkgID] AND
(C.ID = R.CategoryID OR R.CategoryID IS NULL) AND
(S.ID = R.StatusID OR R.StatusID IS NULL)
ORDER BY R.Title, R.Chapter, R.Article, R.Topic, R.SubTopic;