SQL Syntax

[ianjking]

Having trouble figuring this one out for scheduling vacation.

Table staffavailable has two columns: userid and Date
The data would be that user1 on 2004-04-15 would be out of town.
User 1 might have 5 entries in the table for the five days off.

I want to pull a list of available staff by joining with a staff table.

What I am trying to do is return all the staff who do not have an entry listed in the staffavailable table for that day. My current statement is...

SELECT * FROM staff
LEFT JOIN staffavailable ON staff.UID = staffavailable.UID
WHERE staff.employmentStatus = 'y'
AND staffavailable.date != '20040412'
ORDER BY lastName

The problem with this is that if the user has more than one vacation day then they have more than one entry and they are returned as available.

I have been thinking over this for two hours now and my brain hurts, any ideas????

Thanks!

[Nedals]

Try it without the left join.

SELECT * FROM staff, staffavailable
WHERE staff.employmentStatus = 'y'
AND staffavailable.UID = staff.UID AND staffavailable.date != '20040412'
ORDER BY lastName

With the left join the query returns all results from

SELECT * FROM staff WHERE staff.employmentStatus = 'y' ORDER BY lastName

and then fills in any staffavailable data

[ianjking]

Thanks for the idea,

Unfortunately it returns the same results as the first query.

The problem lies in the fact that their are multiple entries in the staffavailable table for a UID, so it is returning the entry for the UID that occurs on a different date.

Basically I need it to search through staff and give me everyone then exclude people from that list who have an entry in staffavailable for that day.

Not even sure if there is syntax to allow for this function.

Thanks

[Nedals]

staff
id name status
1 jones y
2 smith y
3 brown y

staffavailable
1 20040412
2 20040412
1 20040414
3 20040414
2 20040416
3 20040416

Try this

SELECT DISTINCT staff.lastName, staff.colname, staff.colname FROM staff, staffavailable
WHERE staff.employmentStatus = 'y'
AND staffavailable.UID = staff.UID AND staffavailable.date != '20040412'
ORDER BY lastName

NOTE: if the col names are unique to the table, you don't need the table qualifier
ie:

SELECT DISTINCT lastName, colname, colname FROM staff, staffavailable
WHERE employmentStatus = 'y'
AND staffavailable.UID = staff.UID AND date != '20040412'
ORDER BY lastName

ADDED::
If you have staff with the same lastName, use DISTINCT staff.UID instead of DISTINCT staff.lastName