Hi Jakob, You can not do this in MySQL. MySQL does not support hierarchical joins nor does it support Transact-SQL. You will need to do this with a combination of SQL and your favorite host language. Substituting Transact-SQL for Java, Perl, PHP or any other language and you will get the same results.
John Griffin -----Original Message----- From: Jakob Vedel Adeltoft [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 04, 2003 6:39 AM To: [EMAIL PROTECTED] Subject: Looping through parent and childs I'm new to mySQL and would like to know how do a SELECT where I get all child records whose root parent=1 Here's my table (project_group_list): group_id parent_group_id 1 0 2 0 3 1 4 3 5 4 6 4 This is how my output should be: 1 / 3 / 4 / \ 5 6 I was thinking of something like this: SELECT PGL1.project_group_id, PGL1.ref_parent_project_group_id FROM project_group_list PGL1 JOIN project_group_list PGL2 ON PGL1.group_id = PGL2.parent_group_id WHERE PGL1.group_id = 1 The reason for my subject contain "looping" is because I'm also interested in if mySQL have any way of looping SQL queries? Below query is taken from MS-SQL newsgroup: WHILE @@ROWCOUNT>0 BEGIN INSERT INTO [table] SELECT FROM [table] etc. etc. END /Jakob --------------------------------------------------------------------- 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