Hi, For the query that you would need, see this page in the manual: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html
Also see the comment from March 16, 2003 about the LEFT JOIN trick. However, in your case, why don't you just add another column in the tickets table? last_response or whatever. Then you have everything you need right in 1 table and just have to UPDATE the last_response when a response is made. Hope that helps. Matt ----- Original Message ----- From: "brfg3 at yahoo" Sent: Thursday, November 27, 2003 1:00 PM Subject: list, order and limit data > MySQL version: 3.23.49 > OS: Debian3 > Scripting Language: PHP > > I'm working on a trouble ticket system. There are several tables involved, but for this query only two tables are involved: tickets and comments. They are related by the ticketnumber field. This client cannot afford a high end database, and their host does not support MySQL 4 yet. > > I want to display the last 50 trouble tickets and the last response from support for each ticket. I can pull the last fifty with this query: > > "SELECT * FROM tickets LIMIT 50"; > > and I can select the latest date of response from the comments table for a given trouble ticket with this query: > > > SELECT dtg FROM comments WHERE ticketnumber ="540856" ORDER BY dtg DESC LIMIT 1; > > My question is, how can I pull 50 rows from the tickets table and then grab the last resonse date of each ticket from the comments table?. The queries can be run individually easily, but I need them to run together or I need some way of relating the comment table results and the ticket table results. I plan to stick each row in an html table so the user is presented with 50 ticket items, and a link to each item (that part is easy, I just need to know how to pull that query). The reason for the last resonse date is for informational purposes. > > Just to help you visualize (this is in an HTML table): > > | username | submit date | problem class | ticket status | last response | > > the first four fields come from the tickets table, the last comes from the comments table. There might be 20 commenst for each ticket, or there may be none, but I only want to show the date of the last comment. Hopefully I've been clear in what I'm trying to acomplish. > > Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
