Joining tables on most recent update

Dexy · #1 (**permalink**) 04-22-08, 08:51

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

n_i · #2 (**permalink**) 04-22-08, 09:21

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)

Dexy · #3 (**permalink**) 04-22-08, 09:43

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!!

Internet Services

Web Development

Digital Marketing

Consumer Tech