Unanswered: Complicated query to select latest records
Please take a look at my database model in the attachment.
every employee has many cards, every card has a date, and specific areas assigned to it.
my purpose is to view all the employees who has one of the following areas 5,6 or 7 assigned to the LATEST card! i dont need to view employees who have areas 5,6 or 7 assigned to some previous card... i wanna view only latest issued cards...
take a look at my SQL:
SELECT employees.EmployeeID, employees.LastName, employees.FirstName, employees.FathersName, divisions.DivisionName, titles.TitleName, employees.BirthDetails, employees.PassportDetails, employees.Address, employees.PhoneNumber, employees.Details, cards.LetterIN, cards.LetterOUT, cards.CardIssueDate, cards.CardExpireDate, cards.CardDetails, areas.AreaName
FROM titles INNER JOIN ((divisions INNER JOIN employees ON divisions.DivisionID =
employees.DivisionID) INNER JOIN (cards INNER JOIN (areas INNER JOIN rights ON areas.AreaID
= rights.AreaID) ON cards.CardID = rights.CardID) ON employees.EmployeeID =
cards.EmployeeID) ON titles.TitleID = employees.TitleID
WHERE (rights.AreaID = 5 OR 6 OR 7) AND cards.CardIssueDate = max(cards.CardIssueDate) as maxdate
ORDER BY employees.EmployeeID;
i tried different types of code with MAX word... but it seems to work when it stands before FROM word.. how to apply this function in my case?
how can i remake my SQL code to work under my needs?