List:General Discussion« Previous MessageNext Message »
From:Edwin Cruz Date:August 30 2005 10:24pm
Subject:RE: hierarchical db/depth?
View as plain text  
You should see it:
http://www.openwin.org/mike/presentations/hierarchy/hierarchy.html 

Regrads!



-----Original Message-----
From: mel list_php [mailto:list_php@stripped] 
Sent: Tuesday, August 30, 2005 9:01 AM
To: mysql@stripped
Subject: hierarchical db/depth?

hi!
I'm still trying to organize an hierachical db (I saw the last article on
mysql.com: 
http://dev.mysql.com/tech-resources/articles/hierarchical-data.hml).
I began working with what the author calls "adjency list model" (I think the
nested set model is a bit too complex for what I want to do.In addition I
found an equivalent schema in an other application which is close to
mine....and working fine) Basically, I have a table term (term_id, name),
relation
(relation_id,term_id1,term_id2,type_id) and a last table to identify the
relations type id, relation_type (type_id,type_name).

I can retrieve all the info I need with that query:
SELECT t1.term_id2 AS lev0, term0.name AS parent0, trel1.type_name AS rel1,
t1.term_id1 AS lev1, term1.name AS parent1, trel2.type_name AS rel2,
t2.term_id1 AS lev2, term2.name AS parent2, trel3.type_name AS rel3,
t3.term_id1 AS lev3, term3.name AS parent3 FROM relation AS t1 LEFT JOIN
relation_type AS trel1 USING ( type_id ) LEFT JOIN term AS term0 ON (
t1.term_id2 = term0.term_id ) LEFT JOIN relation AS t2 ON t2.term_id2 =
t1.term_id1 LEFT JOIN relation_type AS trel2 USING ( type_id ) LEFT JOIN
term AS term1 ON ( t2.term_id2 = term1.term_id ) LEFT JOIN relation AS t3 ON
t3.term_id2 = t2.term_id1 LEFT JOIN relation_type AS trel3 USING ( type_id )
LEFT JOIN term AS term2 ON ( t3.term_id2 = term2.term_id ) LEFT JOIN term AS
term3 ON t3.term_id1 = term3.term_id WHERE t1.term_id2 =1

It's just adding a new sub-part to the query for each level.
This is working fine on my data sample, because I know that I have exactly 4
levels of depth.
I would like to know if there is a way to have a kind of "auto-extension" of
the query if there is for example a fifth level?
Thanks for any help or tip,
melanie

_________________________________________________________________
Be the first to hear what's new at MSN - sign up to our free newsletters! 
http://www.msn.co.uk/newsletters


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
hierarchical db/depth?mel list_php30 Aug
  • Re: hierarchical db/depth?Peter Brawley30 Aug
  • RE: hierarchical db/depth?Edwin Cruz31 Aug