Joining 3 tables

[Twitch]

Hello PHP patrons,

 

I've been racking my brain over the last few 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 />"; 
} 

 

[shino]

You could try something like:

 

$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 />"; 
} 

 

That's if you have an id auto increment column in your projects table

[Twitch]

shino,

 

Thank you so much for your quick reply.  It worked like a champ, but then I realized I forgot that I also needed to look at the current_employees table to make sure that only current employees are shown, not all that have been employed in the past.  Basically the employee idnum's stored in the 'employees' table are former and current.  The 'current_employees' table only has one column that contains idnum's of current employees.  So how do I add a check to this table to the current code you posted so only current employees show up.  The common column is 'idnum' of course.

 

I tried to add it myself with horrendous results...haha

 

If it's too much to ask, I understand cause you've helped me out a bunch already with the code you did supply.

 

Thanks in advance,

Twitch

[shino]

Based on my previous response, here is what i've added.

You just add an INNER JOIN with your current_employees table, inner join basically returns a result when the table your joining it with matches so the ones that aren't in current_employees won't show up.

 

<?php    
$query = "SELECT employees.* FROM employees INNER JOIN current_employees ON (employees.idnum = current_employees.idnum) 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 />"; 
} 
?>

[Twitch]

Shino, you are DA MAN!  Worked perfectly.  Can't thank you enough for helping me.  That was the last query I needed to finish this little project I'm working on.

 

Thanks a ton!

Twitch