Dates greater/less than or in between?

[Anon]

I'll try to explain this the best that I can...

I have a department table and a employee table. The department table has an ID (111, 222, 333, etc) and a name (111 = accounting, 222 = marketing, etc), and a year in which the department started (111 = 1991, 222=1991, etc). For some reason (not up to me), the department name can change. So, 111 that was accounting could become 111=IT for example. Again, I dunno why this can happen, but apparently it can.

Now, I need to show an employee, and what department he belonged to. So employee 1 belongs to department 222, employee 2 belongs to department 878, etc. However, I need to show what the department name was when he/she was a part of the department.

Dept Name Year
111 Accounting 1991
111 IT 1994
111 Marketing 1999

So, an employee in 111 in 1992 is in Accounting, an employee in 1995 is in IT, and an employee in 2002 is in Marketing.

I need a way to compare the employee date to the department date, and check to see if it is below all department dates, in between 2 dates, or above all dates.

Thanks for any help. I have all kinds of code, but none of it works. I can only get it to check if it is greater than or less than, not if it is both. Thanks again

[Anon]

I need a way to compare the employee date to the department date, and check to see if it is below all department dates, in between 2 dates, or above all dates.

I don't think that's your problem. I think what you need is a way to get the Name of department that has the closest smaller year. So you could do something like this:

SELECT Name FROM table_name WHERE Dept = 111 AND Year < 1995 ORDER BY Year DESC LIMIT 1

That should return IT.

Diego

[Anon]

Thank you. I will give it a try...sounds like it might work. I appreciate it.