Calculating a connectivity matrix from this sort of data is not what the relational database is best designed to do, but it's a common problem - it applies to family tree databases, used-on lists and so on. (What you're actually wanting to do, if I remember the jargon correctly from school, is calculate (part of) the transitive closure of a sparse connectivity matrix.)
I've done this many times, and never found any alternative to running a query round in a loop, once for each "generation", and stopping when two consecutive iterations of the loop produce no new data. How "slow" is slow? With proper indexes and so on the whole thing should surely be under a second? (Of course if you've got more than four or five levels it could be your organisation structure that's broken:-).) Tim Ward Brett Ward Limited - www.brettward.co.uk -----Original Message----- From: Hathaway, Scott L [mailto:[EMAIL PROTECTED]] Sent: 13 May 2002 15:53 To: Mysql (E-mail) Subject: sql question I have an sql question. I have a table that holds user information that includes their id and who they report to. user ----- id (int) supervisor (int) This heirarchy goes several levels deep. Without changing the db structure, what is my easiest way to determine if user x is a supervisor for user y? User x may not be the immediate supervisor, but may be a supervisor or his supervisor, etc. I can do it with PHP by gettign the supervisor, then getting the supervisor's supervisor, etc, but this is slow. Any other ideas will be very much appreciated! Thanks, Scott --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
