Help with Joins please!

[seb2]

Help! I'm not good at JOINs and would like to modify the following sql to allow for another scenario.

To understand better..... think of a 'Filing' as a Mailing... a 'Form' as the 'Mailed Item'.


tblFilingState (Id, FilingForm, State, etc) FilingForm fk to tblFilingForm.Id

tblFilingForm (Id, FormNumber, etc) FormNumber fk to tblForms.Number

tblForms (Number, Title, ParentFormNum, etc) Forms can have children, hense reference to the parent.

What I want to get is a list of all Forms who's 'Number' OR 'ParentFormNumber' equals tblFilingForm.FormNumber. The following line only gets the ones that match 'Number'.

FROM tblStateInfo INNER JOIN (tblForms INNER JOIN (tblFilingForm INNER JOIN tblFilingState ON tblFilingForm.Id = tblFilingState.FilingForm) ON tblForms.Number = tblFilingForm.[Form Number]) ON tblStateInfo.Abbr = tblFilingState.State
ORDER BY tblFilingState.State;

Anyone got suggestions? Need more information???

 

[Sawatzky]

Dear seb2

I did not check whether this is correct, but give it a try:


select tblfilingform.id, [whateverTable].[whateveryouwant]
FROM tblStateInfo,tblForms ,tblFilingForm ,tblFilingState where tblFilingForm.Id = tblFilingState.FilingForm
and tblStateInfo.Abbr = tblFilingState.State
and (tblForms.Number = tblFilingForm.[Form Number] or
ParentFormNumber= tblFilingForm.[Form Number])
ORDER BY tblFilingState.State

regards Astrid