require help with query

[ashiers]

Hi there,

I've created a number of tables which represent a hierarchy. They go as follows:

DIVISIONS

|_ BRANCHES

|_ SECTIONS

|_ SUBSECTIONS
​

Each of these is a table. Each table contains an ID referring to the parent table as follows:

DIVISIONS:
DIVISIONS_ID - AutoIncrement - Primary Key
DIVISION - Text

BRANCHES:
BRANCHES_ID - AutoIncrement - Primary Key
DIVISIONS_ID - Number
BRANCH - Text

SECTIONS:
SECTIONS_ID - AutoIncrement - Primary Key
BRANCHES_ID - Number
SECTION - Text
COST_CENTER - Number

SUBSECTIONS:
SUBSECTIONS_ID - AutoIncrement - Primary Key
SECTIONS_ID - Number
SUBSECTION - Text
COST_CENTER - Number

Object of the query is to obtain each node in the hierarchy when given a specific Cost Center number. A cost center number is unique and will appear in either the SECTIONS or SUBSECTIONS table.

So far this is what I have for a query:

SELECT [DIVISIONS].[DIVISION], [BRANCHES].[BRANCH], [SECTIONS].[SECTION], [SUBSECTIONS].[SUBSECTION]
FROM (DIVISIONS INNER JOIN (BRANCHES INNER JOIN SECTIONS ON [BRANCHES].[BRANCH_ID]=[SECTIONS].[BRANCH_ID]) ON [DIVISIONS].[DIVISION_ID]=[BRANCHES].[DIVISION_ID]) INNER JOIN SUBSECTIONS ON [SECTIONS].[SECTION_ID]=[SUBSECTIONS].[SECTION_ID]
WHERE [SUBSECTIONS].[COST_CENTER]=[Filter by cost center] OR [SECTIONS].[COST_CENTER]=[Filter by Cost Center];

When I run this, it works if the cost center number appears in the SUBSECTIONS table, but if the cost center number appears in the SECTIONS table I get nothing. I thought it might just be a simple matter of changing the WHERE statement from an OR to an AND, but that didn't help.

Please advise,

Alan