Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    43

    Question Unanswered: Complicated query to select latest records

    Hello,
    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:

    Code:
    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?

    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM   cards
    WHERE  issue_date = (SELECT Max(issue_date) FROM cards)
    AND    card_type IN (5, 6, 7)
    [/CODE]
    This WHERE gives you only records that have the MAX issue date.
    The AND part of the where clause filters further on card_type or w/e you called it

    Save this as one query and then create a second that joins to this to get your employee info.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Posts
    43
    I did as u said, and remaked my SQL code, added this statement for now...

    WHERE employees.DivisionID = 5 AND cards.CardIssueDate = (SELECT Max(CardIssueDate) FROM cards)

    but the problem is, it shows only those records who have Max card issue date!

    i want ALL the employees of division-5 be shown, and everyone to be shown with latest card record in cards table

    your code, does show only those employees who have Maximal CardIssueDate

    any ideas? thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •