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;

Hi Rupert,

Maybe I misunderstand, but...

you can nest outer joins within an inner join

inner join:
FROM
(R INNER JOIN X
ON R.ID = X.RuleID)

nest first outer join in inner join:

FROM
(
(R INNER JOIN X
ON R.ID = X.RuleID)
LEFT JOIN S
ON R.CategoryID = S.ID)

nest both outer joins in inner join:

FROM
(
(
(R INNER JOIN X
ON R.ID = X.RuleID)
LEFT JOIN S
ON R.StatusID = S.ID)
LEFT JOIN C
ON R.CategoryID = C.ID)

(the 2 outer parentheses *may* not be needed)


SELECT .....
FROM
((tblRules R INNER JOIN tblPkgRuleXref X
ON R.ID = X.RuleID)
LEFT JOIN tblRuleStatuses S
ON R.StatusID = S.ID)
LEFT JOIN tblCategories C
ON R.CategoryID = C.ID
WHERE
X.PkgID = [Forms]![frmPackages]![PkgID]
ORDER BY
R.Title,
R.Chapter,
R.Article,
R.Topic,
R.SubTopic;


"mscertified" wrote:
>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;