Welcome to WebmasterWorld Guest from 22.214.171.124
Forum Moderators: open
here is users table
user_id level username password firstname lastname
1 a s Aa Bb
2 t tt Brian Adams
3 s s Staff4 351
4 s s8 Staff3 800
here is posts table
post_id ticket_id user_id date
7 KSO-60138 1 18.09.2005 0:53:26
28 QZU-67195 1 24.09.2005 23:14:48
29 ZHK-56918 1 24.09.2005 23:51:01
30 QZU-67195 3 24.09.2005 23:52:34
27 VXQ-20839 3 24.09.2005 23:11:39
26 KSO-60138 3 24.09.2005 23:08:13
25 KSO-60138 3 24.09.2005 23:06:09
18 VXQ-20839 2 18.09.2005 18:48:23
here is tickets table
ticket_id status subject assigned_id
KSO-60138 CUSTOMERPENDING test 3
VXQ-20839 NEW testing su 4
QZU-67195 NEW aaaaaaaaaaa
ZHK-56918 NEW zzzzzzz
The author of ticket should be determined by the first post related to the ticket.
I'm trying to build a query, which will give me
ticket.*, last post date, last post author and all for the given author(user_id).
Can I make that using only one query?
Thanks in advance.
[edited by: txbakers at 1:12 pm (utc) on Sep. 25, 2005]
[edit reason] removed passwords [/edit]
tickets.ticket_id -> posts.post_id
posts.user_id -> users.user_id
tickets.assigned_id -> users.user_id
last post date -> min(posts.date) WHERE user_id=<userid>
But actually I've already solved it by adding couple columns in tickets table(last post id, ticket author id), which is redundant for sure but that was the only quick way to solve it.