> ----------
ID Company StillWorksHere
111 X No
112 Y Yes
112 X No
112 Z No
113 Y Yes
113 Z No
----------
I'm with you part of the way with what you are trying to do. If I
understand this right, firstly you want to find all the people who are
still working somewhere:
SELECT * from YourTable where StillWorksHere = yes
which gives with your data
ID Company StillWorksHere
112 Y Yes
113 Y Yes
Then you want to pick up the people who worked at one company, but now
don't. And also don't work anywhere else:
select YT.ID,* from YourTable as YT where YT.ID not in (select yt.id
from YourTable as YT2 where YT.id=YT2.id and StillWorksHere =yes)
which gives with your data
ID Company StillWorksHere
111 X No.
You can union these queries if you like.
But this still leaves a hole in your results. Lets take a look at
someone who worked at company Z, moved to company Y and then retired.
So the table now looks like this:
ID Company StillWorksHere
111 X No
112 Y Yes
112 X No
112 Z No
113 Y Yes
113 Z No
114 Z No
114 Y No
if I run the unioned query, I get:
ID Company StillWorksHere
111 X No
112 Y Yes
113 Y Yes
114 Y No
114 Z No
See how 114 turns up twice? This is where I get stuck. How do you
intend to tell the difference between the last two lines of the table?
What is it logically that makes one of them right and the other
wrong? As far as I can tell from your post its the date/time of
leaving. You want to show the company that was last recently worked
at. If this is so, then you could attach a date/time field and
populate it with the now() of the time the person left. This might
make your problem easier.
I hope this has been some help to you, if not, please explain again
your criteria.
Cheers
Phil.