homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

GROUP BY behaviour

5+ Year Member

Msg#: 54 posted 10:18 am on Sep 25, 2005 (gmt 0)

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



WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 54 posted 8:34 pm on Sep 26, 2005 (gmt 0)

Welcome to WebmasterWorld, acid.

You have written out the logic in plain text, now simply apply the SQL syntax to it. The only part missing is how to JOIN the tables based on their relation. What do you have so far?


5+ Year Member

Msg#: 54 posted 9:13 pm on Sep 26, 2005 (gmt 0)

Thanks for reply coopster.
The connections as follows.

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.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved