CODEWITHSUNDEEP
mysqlsql
Mollo
Mollo

Reputation: 723

SQL INNER JOIN returning more rows than I need

This is my situation:

I have 2 tables, tickets and tickets-details. I need to retrieve the info inside "tickets" and just the LAST reply from "tickets-details" for each ticket, then show em in a table. My problem is that "ticket-details" returns a row per each reply and I'm getting more than one row per ticket. How can I achieve this in a single query ?

I tried adding DISTINCT into my SELECT but didn't.

I tried using GROUP BY id_ticket but didnt' work too because I wasn't getting the last reply from ticket-details

This is my query:

SELECT DISTINCT ti.id_ticket,ti.title,tiD.Reply,ti.status 
FROM tickets ti 
INNER JOIN ticket-details tiD ON ti.id_ticket = tiD.id_ticket 
WHERE user = '$id_user' ORDER BY status desc

---------------------------------- EDIT-----------------------------------------------

my tables:

tickets(id_ticket, user, date, title, status)

ticket-details(id_ticketDetail, id_ticket, dateReply, reply)

Upvotes: 1

Views: 1894

Answers (3)

peterm
peterm

Reputation: 92785

Assuming that the max id_ticketDetail represents the most recent record in ticket-details you can try

SELECT ti.id_ticket,
       ti.title,
       tiD.Reply,
       ti.status 
  FROM tickets ti JOIN 
(
    SELECT id_ticket, reply
      FROM `ticket-details` d JOIN 
    ( 
        SELECT MAX(id_ticketDetail) max_id
          FROM `ticket-details`
         GROUP BY id_ticket
    ) q ON d.id_ticketDetail = q.max_id
) tiD ON ti.id_ticket = tiD.id_ticket 
 WHERE ti.user = '$id_user' 
 ORDER BY ti.status DESC

or a version with max dateReply

SELECT ti.id_ticket,
       ti.title,
       tiD.Reply,
       ti.status 
  FROM tickets ti JOIN 
(
    SELECT d.id_ticket, d.reply
      FROM `ticket-details` d JOIN 
    ( 
        SELECT id_ticket, MAX(dateReply) max_dateReply
          FROM `ticket-details`
         GROUP BY id_ticket
    ) q ON d.id_ticket = q.id_ticket
       AND d.dateReply = q.max_dateReply
) tiD ON ti.id_ticket = tiD.id_ticket 
 WHERE ti.user = '$id_user' 
 ORDER BY ti.status DESC

Here is SQLFiddle demo for both queries.

Upvotes: 2

martin jrk
martin jrk

Reputation: 185

I do not know Your datatabase model, but if ID is autoincremented you can extend your script with this condition:

SELECT ti.id_ticket,ti.title,tiD.Reply,ti.status 
FROM tickets ti 
 INNER JOIN ticket-details tiD ON ti.id_ticket = tiD.id_ticket 
WHERE user = '$id_user' 
 and tiD.id_ticket in (select max(a.id) from ticket-details a group by a.id_ticket)
ORDER BY status desc

Or if you have some kind of date attribute, change new condition to your date attribute (in my example it is ticked_date )

and tiD.ticked_date in (select max(a.ticked_date) from ticket-details a group by a.id_ticket)

Upvotes: 1

wtcurtis
wtcurtis

Reputation: 26

If you need to grab a bunch of tickets with their ticket-details, adding a subquery would work (though it's a little inefficient):

SELECT DISTINCT 
    ti.id_ticket,ti.title,tiD.Reply,ti.status 
FROM tickets ti INNER JOIN ticket-details tiD 
    ON ti.id_ticket = tiD.id_ticket 
WHERE user = '$id_user' AND 
    tiD = (select id from ticket-details where ticket-details.id_ticket = ti.id_ticket ORDER BY ticket-details.date_field DESC LIMIT 1) ORDER BY status desc

If you only need to do this for one ticket at a time, it's as simple as ordering by the date field in ticket-details and throwing a limit in there:

SELECT DISTINCT 
    ti.id_ticket,ti.title,tiD.Reply,ti.status 
FROM tickets ti INNER JOIN ticket-details tiD 
    ON ti.id_ticket = tiD.id_ticket 
WHERE user = '$id_user' ORDER BY tiD.date_field, status DESC LIMIT 1

Upvotes: 0

Related Questions