Join the same table twice ??

[grissom]

Hi folks, here's my problem

 

Imaging I have one table which shows department names and the payroll number for the manager and deputy for each department

 

table : departments

--------------------------

dept              manager                deputy

-------            -------------              ---------

Sales              0123                      9876

Marketing      2222                      4567

Accounts        5656                      7890

 

 

I also have an employees table as follows :

 

table : employees

-----------------------

payroll              firstname        surname

--------              ------------        -----------

0123                Fred                  Foo

2222                Bert                  Bar

9876                James              Bond

4567                Dick                  Barton

 

 

I would like to do a JOIN query between departments and employees to produce :

 

Department    Mgr first Name    mgr surname      Deputy first name  Deputy surname

---------------    ----------------        ----------------      ----------------------  --------------------

Sales              Fred                    Foo                      James                    Bond

Marketing        Bert                  Bar                      Dick                      Barton

 

 

There is a (slightly long winded) way to do it in PHP by looping through the database, but to do it in one MYSQL statement would mean JOINing the employees table TWICE  (something MYSQL does not appear to like)

 

Is there any really snazzy, slick way of producing the bottom view from the top two tables in one single MYSQL statement ?

 

Cheers and MANY THANKS !

 

[mikosiko]

(something MYSQL does not appear to like)

 

why not?

 

try:

SELECT a.dept      AS Department,
       b.firstname AS MgrFName,
       b.surname   AS MgrSName,
       c.firstname AS DptyFName,
       c.surname   AS DptySName
  FROM departments AS a
    JOIN employees AS b ON a.manager = b.payroll
    JOIN employees AS c ON a.deputy = c.payroll

[grissom]

TA DAH !!!!  THANKS mikosiko !!