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:
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 ?
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