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

[marianne]

i think your database is not normalized. you should read on normalization of database.

to say: you have COST_CENTER - NUMBER on both of your SECTIONS AND SUBSECTIONS tables. Isn't your sections and subsections table related to one another? if it is related then, it is best that you relate the two tables with their SECTIONS_ID. there you can eliminate the cost_center - number on either one of SECTIONS or SUBSECTIONS table.

this way, you will have only one source of cost_center - number to get from. this way, your problem maybe eliminated.

[ashiers]

Hi Marianne,

You're quite right. When I was creating the database I was working from a Tree structure presented to me and as you know tree structures don't always have the same number of levels. In this case, some Sections had a cost center associated, while others went down another fourth level where Sections were split into SubSections. The SubSections also had cost centers associated.

I get what you're getting at though now, so I'm going to make a change by transferring the SECTIONS_ID's and COST_CENTER numbers over to the SUBSECTIONS table and then eliminate the COST_CENTER field from the SECTIONS table.

The end result when I run the query below will then give me what I want.

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];

Thanks for you help. I just needed a fresh pair of eyes to look this over.

Alan