[RESOLVED] Joining 3 tables

[twitch]

Hello PHP Gurus,

I've been racking my brain over the last two days trying to do something and I think I'm close but I need help getting the finishing touch.

I have 3 tables table 1: employees table 2: shifts table 3: projects

Essentially what I'm looking to do is simply show the employees who are clocked in and do not currently have an open project. The code below works for the clocked in part of it. If I clock an employee out, they don't show up anymore, but they show up when clocked in whether they have an open project of not. I'm suspecting that the project status part is looking at all projects and not just the last one. That's the part I'm having trouble with. The project table has a projectID field as the key. So basically the highest projectID per employee is the last project record for that employee. How do I make this script only look at the last project per employee?

Any help is greatly appreciated. My head is about to explode...haha

Thanks in advance,
Twitch

$query = "select *, MAX(fname), MAX(projStatus) from (employees left join shifts on employees.idnum = shifts.idnum) left join projects on shifts.idnum = projects.idnum where shifts.intime > shifts.outtime && projects.projStatus = 'Closed' GROUP BY fname"; 

$who = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($who)){
	echo $row['MAX(fname)']. " - ".$row['lname'];
	echo "<br />";
}

[twitch]

I got some working code so I thought I'd post it here and mark this solved for anyone else in need of similar code.

$query = "SELECT employees.* FROM employees LEFT JOIN shifts ON (employees.idnum = shifts.idnum) WHERE shifts.intime > shifts.outtime AND (SELECT COUNT(id) FROM projects WHERE idnum = shifts.idnum AND projStatus = 'Open' ORDER BY id DESC LIMIT 1) = 0";
$who = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($who)){     

	echo $row['MAX(fname)']. " - ".$row['lname'];     

    echo "<br />"; 
}