PHP SQL - Re: Table linked to itself

Author

Re: Table linked to itself

Kevin Thorpe

2004-04-28, 8:35 am

Michele Locati wrote:
> I have a problem for which I can't see a fast solution:
>
> I have table A which contains, say, 2 fields:
> a_id
> a_parent
> a_parent is linked to the a_id field of the same table, to build a
> hirarchy with a deep which is not known in advance.
>
> I have another table, say B, with 2 fields:
> b_id
> b_a
> Where b_a links B to a record of A.
>
> My problem occurs when I have to retrieve all the B records associated
> with an A record identified with a_id, or any children/grandson/... of
> a_id in A.
>
> What I do now is to retrieve any children of a_id, and any children of
> the children, and so on. Then I select the record of B where b_a is
> any of this big list of children/grandson/....
>
> Anyone can see a faster solution?

SQL isn't any good at recursion. I'm spoiled, in PICK derivatives
there's a SELECT WITHIN..... clause which would recurse any descendants
of a record in A.

I don't know whether you will be able to do this or not, but I've done
this before by using a hierachical key field. eg:
A
AA
AB
ABA
AC
B
BA
BB
You can then use a simple select like to get a chunk of this tree.
Of course moving items will require rewriting the whole index.

Jeppe Uhd

2004-04-28, 9:43 am

Michele Locati wrote:
> I have a problem for which I can't see a fast solution:
>
> I have table A which contains, say, 2 fields:
> a_id
> a_parent
> a_parent is linked to the a_id field of the same table, to build a
> hirarchy with a deep which is not known in advance.
>
> I have another table, say B, with 2 fields:
> b_id
> b_a
> Where b_a links B to a record of A.
>
> My problem occurs when I have to retrieve all the B records associated
> with an A record identified with a_id, or any children/grandson/... of
> a_id in A.
>
> What I do now is to retrieve any children of a_id, and any children of
> the children, and so on. Then I select the record of B where b_a is
> any of this big list of children/grandson/....
>
> Anyone can see a faster solution?
>
> Thank you very much
>
> Michele

It's possible to use the same table more than once in a query with aliasing
(at least in mysql...)

Like this:

SELECT a.name,b.name,c.name FROM a,a AS b,a AS c WHERE a.id=b.parentid AND
b.id=c.parentid;

List returns person,child,grandchild

Hope that helps...

--
MVH Jeppe Uhd - NX http://nx.dk
Webhosting for nørder og andet godtfolk

Michele Locati

2004-04-29, 6:13 pm

Thank you very much for your hints.
I do really appreciate it.
Anyway I found another solution, which doesn't requires any database
extension.
Here it is: I introduced a third table, let's call it C, with 2
fields. In the first one ("c_p") I put all the parent a_id s, and it
the second one ("c_c") any children related to it. So, if the A table
contains the following data:

a_id a_parent
1 NULL
2 1
3 1
4 3
5 NULL
6 5

The C table would look like this
c_p c_c
1 2
1 3
1 4
3 4
6 5

The operations involved in this implementation are:
---when inserting a new top level record
no operation on C
---when inserting a child record in A:
you have to determine any father, grandfather, ... and put one record
in C for anyone of these parents
---when deleting a record of A which has no child:
delete any rows in C where c_p or c_c is a_id

This solution works with any deep of relationship, avoiding to know
how many joins it would be necessary in advance.

Thank you again
Michele

Sponsored Links