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