Query to list parent ticket creators

Moderator: crythias

Post Reply
BSaiago
OTRS newbie
Posts: 1
Joined: 24 Jul 2019, 23:07
OTRS Version?: 6.0
Real Name: Bruno Saiago
Company: Sesc - National Depertament

Query to list parent ticket creators

Post by BSaiago »

Good afternoon everyone,
I have a query that lists all the tickets my team has resolved over the course of a month, all tickets are children,
my supervisor now wants a query to list who are the parent tickets of the tickets we have resolved and who created.
I understand that the link_relation table should be used for this, however I could not generate an efficient query for this.

The query I'm using is:

Code: Select all

SELECT tkt.tn,
       tkt.title AS Assunto,
       tkt.id,
       tkt.user_id,
       tkt.create_by,
       link_relation.source_key,
       link_relation.target_key,
       CONCAT (u.first_name, ' ', u.last_name) as Solver
       CONCAT (c.first_name, ' ', c.last_name) as Creator
FROM ticket tkt
     LEFT JOIN link_relation ON tkt.id = link_relation.target_key
     left join users u on u.id = tkt.customer_id
     left join customer_user c on c.id = tkt.customer_id
WHERE link_relation.target_key IS NOT NULL
AND tkt.create_time >= '2019-06-01'
AND tkt.create_time < '2019-06-30'
ORDER BY tkt.id
The result doesn't bring me the creator nor the solver, Whats am I doing wrong here ? what should be the left join that brings me all the clients that have created a ticket and who solved ?

Post Reply