query to trace all parents

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PYCTAM - 23 Jun 2005 18:31 GMT

Hi,

I have a table with filled out below data:

+------+-----+

|parent|child|

+------+-----+

|A |B |
|B |C |
|B |E |
|C |D |
|E |F |
|E |G |

+------+-----+

So I have to make a query which get all 'parent' values values for
given child value.

For example :
-----------------
If I have to get all parent values for 'D' child., query must get this
values : C, B, A.

If I have to get all parent values for 'F' child., query must get this
values : E, B, A.

If I have to get all parent values for 'C' child., query must get this
values : B, A.

If I have to get all parent values for 'B' child., query must get this
values : A only.
-----------------

Is it possible to create a query which will covers all above conditions
or not using only sql statement without UDF or stored procedures.

Any solutiuons?

Sincerely,
Rustam Bogubaev

Reply to this Message

mAsterdam - 23 Jun 2005 18:55 GMT

Please don't spoil while helping :-)

Reply to this Message

Stefan Rybacki - 24 Jun 2005 12:58 GMT

> Hi,
>
[quoted text clipped - 36 lines]
> Sincerely,
> Rustam Bogubaev

Another table structure could help

Reply to this Message

Mark D Powell - 24 Jun 2005 14:13 GMT

And what database is in use? If you have Oracle then you can use the
connect by clause to solve this:

UT1 > set echo on
UT1 > drop table pc;

Table dropped.

UT1 > create table pc ( parent varchar2(01), child varchar2(01) );

Table created.

UT1 > insert into pc values ('A','B');

1 row created.

UT1 > insert into pc values ('B','C');

1 row created.

UT1 > insert into pc values ('B','E');

1 row created.

UT1 > insert into pc values ('C','D');

1 row created.

UT1 > insert into pc values ('E','F');

1 row created.

UT1 > insert into pc values ('E','G');

1 row created.

UT1 >
UT1 > select parent
2 from pc
3 connect by child = prior parent
4 start with child = 'C'
5 /

P
-
B
A

UT1 > select parent
2 from pc
3 connect by child = prior parent
4 start with child = 'F'
5 /

P
-
E
B
A

UT1 > select parent
2 from pc
3 connect by child = prior parent
4 start with child = 'B'
5 /

P
-
A

Oracle even provides functions that work with connect by to show the
full path etc...

SQL> l

1 select sys_connect_by_path(ename,'_')
2 from emp
3 connect by mgr = prior empno
4* start with ename = 'KING'
SQL> /

SYS_CONNECT_BY_PATH(ENAME,'_')
--------------------------------------------------------------------------------
_KING
_KING_JONES
_KING_JONES_SCOTT
_KING_JONES_SCOTT_ADAMS
_KING_JONES_FORD
_KING_JONES_FORD_SMITH
<snip>

Don't have Oracle. Well Joe Celko wrote a whole book on dealing with
hierarchies using SQL.

HTH -- Mark D Powell --

Reply to this Message

Mark D Powell - 24 Jun 2005 14:39 GMT

Sorry about asking what database. I had open mutiple screens and
though I was on the theory borad. I do not think that MS SQL Server
has a "connect by" clause unless the 2005 version introduces it so you
will need to design your process using either multiple tables or
procedures.

-- Mark D Powell --

Reply to this Message

Stefan Rybacki - 24 Jun 2005 17:03 GMT

> Sorry about asking what database. I had open mutiple screens and
> though I was on the theory borad. I do not think that MS SQL Server
> has a "connect by" clause unless the 2005 version introduces it so you
> will need to design your process using either multiple tables or
> procedures.

No you don't. "Nested Sets" is a keyword.

> -- Mark D Powell -->

Reply to this Message

Neo - 24 Jun 2005 16:22 GMT

> |parent|child|
> +------+-----+
[quoted text clipped - 6 lines]
> ... 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)

Reply to this Message

-CELKO- - 24 Jun 2005 16:40 GMT

Look up the "nested sets model" on Google or buy a copy of TREES &
HIERARCHIES IN SQL. You do not need procedural code or recursion.

Reply to this Message