## hierarchy table design question

### hierarchy table design question

Hello,

Let say I have the following schema.

Table A has a 1-M relationship with Table B.
Table B has a 1-M relationship with Table C.

I want to get related rows from Table C by given
Table A's primary key. The following is pseduo
code from application layer.
1. Get all related rows from B by providing A's pk
2. For each row of B, get all rows from c.

U see if I have 1..n rows of B, then I have to
loop through 1..n times to retrieve Cs. All these
code is from app layer, and therefor it is
involved the network traffic..etc.

What is the best way to solve this problem?

Thanks,
D. T.

Sent via Deja.com http://www.deja.com/

### hierarchy table design question

Hello,

I have the following table schema:

Table A has 1-M relationship with Table B.
Table B has 1-M relationship with Table C.

I want to get all rows from C by given the primary key of A.
The following is pesudo code from application layer.

1. gets all rows of B by providing primary key of A.
2. for each row of B, gets all rows from C.

U see if I will have 1..n number of rows from B, and I have to loop
through each in order to get rows of C. It is really expensive way for
doing it.

Should I denormalized these tables? .... Any suggestion?
Thanks,
D. T.

Sent via Deja.com http://www.deja.com/

### hierarchy table design question

With SQL:
select TC.* from TA, TB, TC Where TA.pk = [whatever] and TA.pk = TB.fk and
TB.pk = TC.fk

pk=primary key
fk=foreign key

HTH,
Jon Myers

>Hello,

>Let say I have the following schema.

>Table A has a 1-M relationship with Table B.
>Table B has a 1-M relationship with Table C.

>I want to get related rows from Table C by given
>Table A's primary key. The following is pseduo
>code from application layer.
>1. Get all related rows from B by providing A's pk
>2. For each row of B, get all rows from c.

>U see if I have 1..n rows of B, then I have to
>loop through 1..n times to retrieve Cs. All these
>code is from app layer, and therefor it is
>involved the network traffic..etc.

>What is the best way to solve this problem?

>Thanks,
>D. T.

>Sent via Deja.com http://www.deja.com/

I have a table that has the following:
Employee Name
Employee Number
Employee Number of immediate supervisor

I need to build an organization chart using this information (All the people
that report to a manager then senior manager, then vp).  In this
organization, it is possible for employees to report to someone of the same
level.  Can anyone recommend an efficient way of building this query or
multiple queries?

Thanks, Steven