November 24, 2009 at 4:08 pm
I have these two tables:
ost_ticket
--------------
subject
ticketID
ticket_id
status
ost_ticket_response
--------------
response
response_id
ticket_id
Contents of the tables:
----------
ost_ticket
----------
subjectticketIDticket_idstatus
------------------------------
Remove Screws16035515closed
-------------------
ost_ticket_response
-------------------
responseresponse_idticket_id
----------------------------
screws need removed1615
ordering2215
Done2415
And here's my query:
SELECT ost_ticket.subject, ost_ticket.ticketID, ost_ticket.ticket_id, ost_ticket_response.response, ost_ticket_response.response_id
FROM ost_ticket
INNER JOIN ost_ticket_response ON ost_ticket.ticket_id = ost_ticket_response.ticket_id
WHERE ost_ticket.status = 'closed'
AND ost_ticket_response.response_id
IN (SELECT MAX(response_id)
FROM ost_ticket_response
WHERE ticket_id = ost_ticket.ticket_id)
Here's my problem. In the ost_ticket_response table I need to grab both the min response_id and the max response_id. However, I want only the first and last response IDs, and nothing in the middle. Right now here's the output of the query above:
subjectticketIDticket_idresponseresponse_id
-------------------------------------------
Remove Screws160355 15 Done 24
What I would like is the following:
subjectticketIDticket_idresponseresponse_idresponse2response_id2
----------------------------------------------------------------
Remove Screws160355 15 Done 24screws need removed16
Any guidance?
November 24, 2009 at 4:28 pm
Welcome to the forums. This should get you pretty close, but it's untested; so I may have screwed up an underscore or 12. To get tested code in the future, please refer to the post in my signature on how to provide sample data.
SELECT T.subject, T.ticketID, T.ticket_id,
RMax.response, RMax.Response_ID,
RMin.response response2, RMin.Response_ID response_id2
FROM ost_ticket T
INNER JOIN (SELECT Ticket_ID, MAX(Response_ID) MaxResp, MIN(Response_ID) MinResp
FROM ost_ticket_response
GROUP BY Ticket_ID) RMM ON T.Ticket_ID = RMax2.Ticket_ID
LEFT JOIN ost_ticket_response RMax ON T.Ticket_ID = RMax.Ticket_ID AND RMax.Response_ID = RMM.MaxResp
LEFT JOIN ost_ticket_response RMin ON T.Ticket_ID = RMin.Ticket_ID AND RMin.Response_ID = RMM.MinResp
WHERE T.status = 'closed'
November 24, 2009 at 5:31 pm
Wow, thanks for the quick reply.
I ran the query but it kept complaining that 'RMax2.Ticket_ID' was an unknown column. So I modified it a bit and it seems to work. Does this look correct?
SELECT T.subject, T.ticketID, T.ticket_id,
RMax.response, RMax.Response_ID,
RMin.response response2, RMin.Response_ID response_id2
FROM ost_ticket T
INNER JOIN (SELECT Ticket_ID, MAX(Response_ID) MaxResp, MIN(Response_ID) MinResp
FROM ost_ticket_response
GROUP BY Ticket_ID) RMM ON T.Ticket_ID = RMM.Ticket_ID
LEFT JOIN ost_ticket_response RMax ON T.Ticket_ID = RMax.Ticket_ID AND RMax.Response_ID = RMM.MaxResp
LEFT JOIN ost_ticket_response RMin ON T.Ticket_ID = RMin.Ticket_ID AND RMin.Response_ID = RMM.MinResp
WHERE T.status = 'closed'
Thanks again for your help!
November 24, 2009 at 7:43 pm
Yep, I changed the aliases a few times and missed that one, my fault. (The parser would have caught that one if I had checked it, so that one is on me regardless of sample data). Looks like you sorted it though.
November 30, 2009 at 8:10 am
It works like a dream. Thanks for all your help.
November 30, 2009 at 8:38 am
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply