Need help with SQL Query

[shripaldalal]

Hi,

I have a table with four fields:

Group Under Group Primary Group
===== =========== ============
Sundry Debtors Current Assets Current Assets
North Sundry Debtors Current
Assets
UK North Current
Assets

So the group Highest Group is Current Assets -> Sundry Debtors ->
North -> UK
UK comes in North and North comes in Sundry Debtors.

Now if I want all Sundry Debtors groups I can run an SQL Query as:

select * from groupmaster where group = 'Sundry Debtors' or [Under
Group] = 'Sundry Debtors'

But this will only return Sundry Debtors and North but not UK.

How do i run a query that will return 'Sundry Debtors', North and UK
all three when I want all groups below Sundry Debtors.

Thanks,
Shripal Dalal.

 

[MichaelRay via AccessMonster.com]

That's pretty difficult with the current table structure. Another way of
doing it is to assign a hierarchy of relationships in this table. Add a
GroupID field, an integer that uniquely identifies each group.
Then for the Under Group and Primary Group, use the Group ID fields (assuming
you're only going to have a 3-level hierarchy)

Using the schema, your table would look like
Group ID Group Name UnderGroupID PrimaryGroupId
1 Current Assets 0 0
2 Sundry 1 0
3 North 2 1
4 UK 3 2

Then your where clause would be "where GroupID = 2 or UnderGroupID = 2 or
PrimaryGroupID = 1 or PrimaryGroupID = 2. This should return all three
groups.

Of course you could use the Group Names is instead of IDs if you choose,
although using IDs is more standard normalization.

Hi,

I have a table with four fields:

Group Under Group Primary Group
===== =========== ============
Sundry Debtors Current Assets Current Assets
North Sundry Debtors Current
Assets
UK North Current
Assets

So the group Highest Group is Current Assets -> Sundry Debtors ->
North -> UK
UK comes in North and North comes in Sundry Debtors.

Now if I want all Sundry Debtors groups I can run an SQL Query as:

select * from groupmaster where group = 'Sundry Debtors' or [Under
Group] = 'Sundry Debtors'

But this will only return Sundry Debtors and North but not UK.

How do i run a query that will return 'Sundry Debtors', North and UK
all three when I want all groups below Sundry Debtors.

Thanks,
Shripal Dalal.