SQL help! this hould be easy?

[mythix]

Right, I am developing a ticket system. I have two tables:

mos_wats_ticket
watsid int(11)
ticketid int(11)
ticketname varchar(25)
vis tinyint(1)
open tinyint(1)
datetime timestamp(14)

mos_wats_msg
msgid int(11)
ticketid int(11)
watsid int(11)
msg text
datetime timestamp(14)

And I display all tickets bt executing an SQL query that selects tickets in order of datetime - note this is the submited datetime. What i would like it to display a list of tickets ordered by the last message datetime - much as threads are ordered in this forum.

Can I use SQL to do this or will I need to script this?

[mythix]

Hey come on! somebody this is an easy ine, I'm sure of it with all ur SQL brains you should be able to answer this one for me

[r937]

what are the relationships between those two tables? how would they be joined? is there a one-to-many relationship between them, and which way (1-to-m or m-to-1) does it go?

[mythix]

ooo a reply, i forgot all about this post

one ticket to many posts where mos_wats_tickets.ticketid=mos_wats_msg.ticketid

[r937]

"display a list of tickets ordered by the last message datetime"

select t.watsid
     , t.ticketid
     , t.ticketname
     , t.vis
     , max(m.`datetime`) as lastpost
open
  from mos_wats_tickets as t
left outer
  join mos_wats_msg as m
    on t.ticketid
     = m.ticketid  
group
    by t.watsid
     , t.ticketid
     , t.ticketname
     , t.vis
order
    by lastpost desc