Double data fetch in SQL box

[gecelann]

Hi everyone!

Good day!
Please help me to fix my query, it is returning double data when I run it thru SQL BOX please
see the screenshot for reference.

Code: Select all

SELECT concat(u.first_name," ",u.last_name) owner,
       ts.name state,
       t.tn ticket_number,
       t.customer_user_id customer_id,
       t.customer_id customer,
       th.create_time start_time,
       dfv1.value_text category,
       a.a_body latest_update,
       Coalesce((SELECT th2.create_time
                 FROM   ticket_history th2
                 WHERE  th2.history_type_id IN ( '1', '27' )
                        AND th2.ticket_id = th.ticket_id
                        AND th2.id > th.id
                 LIMIT  1), Now())
       end_time,
       TIMEDIFF(Coalesce(
       (SELECT th2.create_time
        FROM   ticket_history th2
        WHERE  th2.history_type_id IN (
               '1', '27' )
               AND th2.ticket_id =
                   th.ticket_id
               AND th2.id > th.id
        LIMIT  1), Now()),th.create_time) time_range
FROM   `ticket_history` th
       LEFT JOIN users u
              ON u.id=th.create_by
       LEFT JOIN ticket_state ts
              ON ts.id = th.state_id
       LEFT JOIN ticket t
              ON t.id=th.ticket_id
       INNER JOIN dynamic_field_value dfv1
              ON th.ticket_id = dfv1.object_id
       INNER JOIN article a
              ON a.ticket_id = t.id
WHERE  th.history_type_id IN ( '1', '27' )
AND    ts.id != '2'
AND dfv1.field_id =4
AND    t.create_time 
BETWEEN '2016-06-10 00:00:00' AND '2016-08-22 23:59:59'
ORDER BY t.id

please help me to fix it

[skullz]

GROUP BY t.id just before ORDER BY

[gecelann]

GROUP BY t.id just before ORDER BY

Thanks skullz!
But unfortunately it doesn't count the other states that I need in my report.
I just need the latest article body attached in my report. but the current query that I have is duplicating the data.

[gecelann]

GROUP BY t.id just before ORDER BY

Actually I have the exact result I just need the updated article body in each state.

Code: Select all

SELECT concat(u.first_name," ",u.last_name) owner,
       ts.name state,
       t.tn ticket_number,
       t.customer_user_id customer_id,
       t.customer_id customer,
       th.create_time start_time,
       dfv1.value_text category,
       a.a_body latest_update,
       Coalesce((SELECT th2.create_time
                 FROM   ticket_history th2
                 WHERE  th2.history_type_id IN ( '1', '27' )
                        AND th2.ticket_id = th.ticket_id
                        AND th2.id > th.id
                 LIMIT  1), Now())
       end_time,
       TIMEDIFF(Coalesce(
       (SELECT th2.create_time
        FROM   ticket_history th2
        WHERE  th2.history_type_id IN (
               '1', '27' )
               AND th2.ticket_id =
                   th.ticket_id
               AND th2.id > th.id
        LIMIT  1), Now()),th.create_time) time_range
FROM   `ticket_history` th
       LEFT JOIN users u
              ON u.id=th.create_by
       LEFT JOIN ticket_state ts
              ON ts.id = th.state_id
       LEFT JOIN ticket t
              ON t.id=th.ticket_id
       INNER JOIN dynamic_field_value dfv1
              ON th.ticket_id = dfv1.object_id
       LEFT JOIN article a
              ON a.ticket_id = t.id
WHERE  th.history_type_id IN ( '1', '27' )
AND    ts.id != '2'
AND    dfv1.field_id =4
AND    t.create_time 
BETWEEN '2016-06-10 00:00:00' AND '2016-08-22 23:59:59'
GROUP BY th.create_time
ORDER BY t.id

Selection_112.png

As you can see here in my screenshot, the data in latest_update (article body) is a duplicate data I need the updated article body just like the screenshot in my post earlier.

[skullz]

i only can give an example here..
please modify and add to suit your need

Code: Select all

SELECT t.tn, t.id, t.title,
SUBSTRING_INDEX(GROUP_CONCAT(a.a_body SEPARATOR '||'), '||', 1 ) AS Description, 
t.create_time AS TICKET_CREATE_TIME,
SUBSTRING_INDEX(GROUP_CONCAT(a.a_body SEPARATOR '||'), '||', -1 ) AS Last_Update, 
SUBSTRING_INDEX(GROUP_CONCAT(a.create_time SEPARATOR '||'), '||', -1 ) AS Last_Update_Time
FROM ticket t 
LEFT JOIN article a ON (t.id=a.ticket_id)

WHERE (t.create_time LIKE '2016%') 

GROUP BY t.id

[gecelann]

i only can give an example here..
please modify and add to suit your need

Code: Select all

SELECT t.tn, t.id, t.title,
SUBSTRING_INDEX(GROUP_CONCAT(a.a_body SEPARATOR '||'), '||', 1 ) AS Description, 
t.create_time AS TICKET_CREATE_TIME,
SUBSTRING_INDEX(GROUP_CONCAT(a.a_body SEPARATOR '||'), '||', -1 ) AS Last_Update, 
SUBSTRING_INDEX(GROUP_CONCAT(a.create_time SEPARATOR '||'), '||', -1 ) AS Last_Update_Time
FROM ticket t 
LEFT JOIN article a ON (t.id=a.ticket_id)

WHERE (t.create_time LIKE '2016%') 

GROUP BY t.id

Thank you so much!