need help with a complex mysql join query

[mjfroggy]

Hello,

I need help with a complex mysql query.
What I need to do is connect I have a table with staff and there supervisors. I also have another table that lists if the staff member is still active or not.

So with my query I need to connect to the one table and pull the latest entry for all staff who are connexted to a specific supervisor I then need to connect to the other table to see if they are active or not. With the end result from the mysql query being a list of all active staff that are curretnly assigned to a specific supervisor.

My issue is I am not sure how to do the join to the table that lists if the person is active or not. Here is my query code

$month = '9';
$year = '2009';
$groupwiseid = 'jdoe';

SELECT teams.rep
     , teams.assign_date, Employees.Employeestatusid  
  FROM ( SELECT rep
              , MAX(assign_date) AS assignment
           FROM teams, Employees
          WHERE MONTH(assign_date) <= $month AND YEAR(assign_date) = $year 
         GROUP
             BY rep ) AS latest
INNER
  JOIN teams
    ON teams.rep         = latest.rep
   AND teams.assign_date = latest.assignment, Employees ON Employees.GroupwiseID
 WHERE sup_manid = '$groupwiseid' 

When I run the above in sql I get the following error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON Employees.GroupwiseID WHERE sup_manid = 'imoss' LIMIT 0, 30' at line 12

[debtboy]

It would be better if you gave the table structures (schema).
I'll have to make some assumptions, but your query should
probably look something like this:

SELECT teams.rep,
       max(teams.assign_date) AS assign_date,
       Employees.Employeestatusid
FROM teams INNER JOIN Employees
ON teams.rep = Employees.rep
GROUP BY teams.rep, Employees.Employeestatusid

It's hard for me to understand what your exact criteria is,
but it would be placed in a WHERE clause located after
the ON line and before the GROUP BY line.

Need more info for a more detailed solution.
Good Luck

[cdg10620]

Whenever you do a join it needs to say JOIN table1.column = table2.column. I'm assuming you have a syntax error due to the fact that you are saying JOIN ON Employees.GroupwiseID but you do not have "Employees.GroupwiseID" equal to another value. I can't tell much else without a table schema like debtboy stated above. If you have any other questions please let me know.