Re: query to trace all parents

From: Neo <neo55592_at_hotmail.com>
Date: 24 Jun 2005 08:22:50 -0700
Message-ID: <1119626570.411888.320320_at_g43g2000cwa.googlegroups.com>

> |parent|child|
> +------+-----+
> |A     |B    |
> |B     |C    |
> |B     |E    |
> |C     |D    |
> |E     |F    |
> |E     |G    |
> ... query [to find parents] using only sql statement
> without UDF or stored procedures.

To store/query the described data in a small alternate db, copy and paste the script shown below into app's input box and press submit button. To view the data, expand tree node labeled "person". Db handles normalization, PKs and FKs. For more info, see www.xrdb.com/example

// Create type person and make it an item of main directory. // Note: inst is short for instance.
(CREATE type inst *person & dir item it)

// Create persons.
(CREATE person inst *adam)
(CREATE person inst *bob)
(CREATE person inst *charle)
(CREATE person inst *doug)
(CREATE person inst *erwin)
(CREATE person inst *fred)
(CREATE person inst *george)

// Create relationships between persons.
// Note: the verbs parent and child are already in db,
// thus no need to create them.
// Note: Enable tree to load parent relationships via View Menu
// prior to executing below queries.

(CREATE adam child bob)
(CREATE bob parent adam)

(CREATE bob child charle)
(CREATE charle parent bob)

(CREATE bob child erwin)
(CREATE erwin parent bob)

(CREATE charle child doug)
(CREATE doug parent charle)

(CREATE erwin child fred)
(CREATE fred parent erwin)

(CREATE erwin child george)
(CREATE george parent erwin)

// Find doug's parents recursively.
// Returns charle, bob, adam.
(SELECTR doug parent)

// Find fred's parents recursively.
// Returns erwin, bob, adam.
(SELECTR fred parent)

// Find charle's parents recursively.
// Returns bob, adam.
(SELECTR charle parent)

// Find bob's parents recursively.
// Returns adam.
(SELECTR bob parent)
Received on Fri Jun 24 2005 - 17:22:50 CEST