mysql query two tables please Help

Mike1569 · 07-08-2009, 12:02 PM

I feel like I’m so close but cant get there, I have two tables, employees and job employees holds all employees data and job holds time card info. I’m making a form that displays the employees that have turned in time cards and the ones that have not. I can get the ones that have

Code:

$query1 = "SELECT DISTINCT Employee FROM job where Date='$date'";

But getting the ones that have not is a lot harder this is what I have

Code:

$query2 = "SELECT DISTINCT a.first_name
    FROM employees a
    left outer join job b
        on a.first_name=b.employee
    WHERE a.level='5' and b.date !='$date'";

This works fine tell the next day when employees post that days cards it will show them as both having a card and not having a card. The level= filters the list for shop employees or inactive employees
any ideas

Mike1569 · 07-08-2009, 01:25 PM

Found the sulution

Code:

$query2 = "SELECT first_name FROM employees where first_name not in (SELECT employee FROM job where Date='$date') and level='5'";

It works yaaaaaa Thanks you all you help

chrism01 · 07-09-2009, 12:31 AM

In the long run you'll find having/using an employee_id is guaranteed to keep people separate when they have the same name.