Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: Joining tables on most recent update

    Hi, newbie here. I've done a search for this and couldn't find anything - if there are any similar questions that have been resolved then please post the link and disregard this post! Also, if this is in the wrong place, feel free to move it.

    Basically I have two tables, Ticket and TicketUpdate as follows:

    Ticket ( ID, Problem, Status, Priority, LoggedTime, CustomerID , ProductID )
    TicketUpdate ( ID, Message, UpdateTime, TicketID , StaffID )

    where Status is either 'open' or 'closed', and LoggedTime and UpdateTime are both timestamps. Every time an update is made to a ticket, a new record is entered in the TicketUpdate table, so one Ticket can have many TicketUpdates.

    What I am trying to do is display the Ticket information along with the most recent TicketUpdate. Everything I try with joins and max(UpdateTime) doesn't seem to work. Any help you can give would be greatly appreciated.

    Edit: I'm using DB2 v8.1 on Windows XP Pro if it's important.

    Thanks!
    Dexy
    Last edited by Dexy; 04-22-08 at 09:59.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    select * from ticket t, ticketupdate u 
    where t.id=u.id and 
    u.updatetime = (select max (u1.updatetime) from ticketupdate u1 where u1.id=u.id)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    I had to make a few adjustments,

    Code:
    select t.id, problem, status, priority, loggedtime, customerid, productid, updatetime from ticket t, ticketupdate u 
    where t.id = u.ticketid and
    u.updatetime = (select max (u1.updatetime) from ticketupdate u1 where u1.ticketid=u.ticketid)
    probably due to me not being clear enough. But it's working exactly how I wanted now, thank you!!

Posting Permissions

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