>Dear,
> I have an table which like to use recursive join but I wonder
>how to use it. Please see the table struc below.
> emp.id,emp name, emp salary, emp head-id
> 001,mr. a,10000,001
> 002,mr. b,5000,001
> 003,mr. c,5000,003
> 004,mr. d,5000,003
> Where emp. head-id is id from emp.id. If I'd like to know
>which head count name of id 002 (it must shown name from id 001).
>How could I do with mysql?
>
>Regards,
>Sommai Fongnamthip
Sir, you are trying to deal with a tree structure. I'm going to do
something I've never done before and recommend that you read Joe
Celko's book 'SQL For Smarties', which contains two chapters on
dealing with tree structures.
The table above uses the adjacency list model. The following is
modified from Celko's book and returns all boss/subordinate pairs:
SELECT e1.name, 'is the boss of', e2.name
FROM emp e1, emp e2
WHERE e1.id = e2.head_id;
To return the boss 2 levels up, use
SELECT e1.name, 'is the boss of the boss of', e3.name
FROM emp e1, emp e2, emp e3
WHERE e1.id = e2.head_id AND e2.id = e3.head_id;
Good luck
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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
