I have a staff database which contains a column that has a "reports to"
field.
If I have a table like this:
ID Name Reports To
------------------------
1 Graeme 5
3 Bob 6
5 Sue 3
How can I (in one query) find the next 2 levels of people that Graeme
reports to?
Ie, I want to get the following from the query when I search for Graeme:
Graeme, Sue, Bob
Is this possible? I presume it would be with a LEFT JOIN, but I can't
figure out how to change the name of the table for the second JOIN.
Here is my current query:
SELECT S.first, S.last, S.reportsto, staff.first, staff.last,
staff.reportsto, S2.first, S2.last, S2.reportsto FROM staff AS S, staff AS
S2 LEFT JOIN staff ON S.reportsto=staff.id WHERE S.first='Graeme' AND
staff.id=S2.id
Any ideas?
Thanks!
---------------------------------------------------------------------
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
