Parent / Child Query

  • Thread starter Thread starter MJS
  • Start date Start date
M

MJS

I have a simple table 'Panel' w/ the followning schema: Source, Panel
Panel is a primary key
there is a 1:many relationship between Source and Panel, i.e., each source
(which is also a Panel) can have many Panels connected to it (re below):

I need a parametric query to that will return all panels fed from a
specified source, including all it's children. In this case, if I were to
query based on MSB, I would return all panels. However, if I were to query
based on HVA3, it would only return HVB3 and HA3.

Thanks,
MS


Panel, Source

LA1, HBA1

LC, HC

LM, HM

HA2, HVA2

LA2, HVA2

HA3, HVA3

HVB3, HVA3

LB, HVB

HE1, HVE1

LE1, HVE1

LE2, HVE2

LB2, LB

LC2, LC

LE4, LE1

LE3, LE2

HVA2, MSB

KH, MSB

KA, MSB

HM, MSB

HVA3, MSB

MCC1, MSB

HVA1, MSB

HC, MSB

DM, MSB

HVE1, MSB

HVB, MSB

MCC2, MSB

HVE2, MSB

MSB, Service
 
You have a table that his self-joined, and you want to list all the children
of the panel, through all the generations. Not a simple thing with SQL.

Search for "bill of materials". As a starting point, see:
http://www.mvps.org/access/modules/mdl0027.htm

More in-depth stuff:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

There is also a problem of infinite regression, where a child record becomes
one of its own parents. Whether intentionally, or as a data entry error, it
is something you have to consider. One of the things I have done when
working with this kind of data is to specify a limit on the depth to which
this can go, and programmatically populate a temp table with all the
children to that depth. You can then use it as a normal JOIN in a query to
get all the offspring instantly, so the time it takes to generate the temp
table each time you need it more than saves the time trying to follow all
the nodes of the tree.
 
Thanks for the verification that this propabaly isn't solvable by a SQL
statement(s).

I created a recursive function to accomplish this, but your point on a child
becoming a parent to itself is well taken. Logically, this should never
happen for the engineering analysis I am performing.... but like you said,
the user could enter bad data.

Thanks for the reply.
MS
 
Oracle actually has a solution for this, since they created an addition to
standard SQL, where one can use a recursive call in SQL statements called
'CONNECT BY PRIOR'. This allows to drill down through all hierachy levels, or
to drill up from any sub level.

Also an addition has been made to SQL '99 that is similar to the Oracle
functionality, it is just called 'WITH RECURSIVE' in SQL '99.

I have tried to find a similar solution to be used in Acess 2002, but so far
without any luck.

MJS - If you have created a function to do this, could it be possible for me
to geta copy of this instead of having to write the function myself ?

Regards

Verner0805
 
Back
Top