cool, thanks! I don't know why I was using LEFT JOINS :)
What would I do if I wanted to go the other way... ie see everyone who
reports to Bob?
Also, won't this query return blank if you can't go "up" 2 levels to S3?
Thanks!
Graeme
----- Original Message -----
From: "Dibo Chen" <[EMAIL PROTECTED]>
To: "Graeme B. Davis" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, May 18, 2001 2:53 PM
Subject: Re: Hierarchy Queries - Multiple LEFT JOINs of the same table?
> try:
>
> select s3.name from staff s1, staff s2, staff s3
> where s1.id = 1 and s1.reportsto = s2.id and s2.reportsto = s3.id
>
> "Graeme B. Davis" wrote:
> >
> > 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
>
---------------------------------------------------------------------
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
