This will get a manager of a team which contains a team which contains
individual nr 5.
SELECT I.Name FROM
TEAMREPORT T3
TEAMREPORT T2
TEAMREPORT T,
INDIVIDUAL I,
WHERE T.individualId = 5
AND t.teamid = t2.indivdualID
AND t3.IndividualId = t2.teamID
AND T3.IndividualID = I.IndividualID
AND I.Manager = 1.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: maandag 24 september 2001 17:40
To: [EMAIL PROTECTED]
Subject: Oracle CONNECT BY
Howdy all...
New to MySQL and I've got a question. I have an application that does a
good deal of hierarchical reporting. In order to do this one of the tables
in my database contains nothing more than an individuals id and the team to
which that individual reports
TABLE - TEAMREPORT
e.g. Individual ID(PK,FK) Team ID
_____________________________
|1 |123 |
_____________________________
|2 |123 |
_____________________________
|3 |456 |
_____________________________
|4 |789 |
_____________________________
|5 |789 |
_____________________________
I can get much more information by linking this table to another table:
TABLE - INDIVIDUAL
IndividualID Date Name Manager
_________________________________________________
|1 |2001-09-01 |Smith |1 |
_________________________________________________
|2 |2001-09-01 |Black |0 |
_________________________________________________
|3 |2001-09-01 |Anderson |1 |
_________________________________________________
|4 |2001-09-01 |Paul |1 |
_________________________________________________
|5 |2001-09-01 |Wells |0 |
_________________________________________________
(The Manager field tells me if that person is the manager of the team;
1=Manager, 0=NotManager)
So if I wanted to find out what team individual #4 reported to I would
simply SELECT TEAMID FROM table WHERE INDIVIDUAL ID = 4.
If I wanted to find out who was the manager of team 789: SELECT I.Name FROM
TEAMREPORT T, INDIVIDUAL I, WHERE T.IndividualID = I.IndividualID AND
Manager = 1.
Of course the problem at hand is a little more complex. It is possible to
have more than 1 level of hierarchy. In other words, team 789 reports to
team 123. So, in plain English, Individual 5 reports to Team 789 which, in
turn, reports to Team 123. What I would like to find out is, if I start
with individual #5 and #5 reports to team 789, which also reports to team
123, who is the Manger of team 123 (I need the manager 2 levels up rather
than 1)? Of course a subquery would be ideal in this situation, but
obviously this cannot happen. I know that many subqueries can be turned
into a join (or multiple join) statement but I cannot figure out that
statement for the life of me. I'd hate to turn one query into two, but at
this point my SQL inexperience is forcing me to do so.
Any SQL wizards have any ideas?
If I was using Oracle, the CONNECT BY clause would be an option. Is there
anything similar in MySQL?
Hope this makes sense and I would love to hear any thoughts.
T.J.
---------------------------------------------------------------------
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
