I currently have a program in which I want to list ‘support tickets’ in a grid.
The tickets are coming from a SQL DB out of a ticketing system.
A ticket has an activity, example:
I have a fully functional design but have a problem to list the following:
What I want to do in my program is to list the parent tickets, with their child tickets as children (details). However, with the added complexity, i’m obliged to do this recursively.
Example:
My questions were:
In this case, is the recursive lookup to be done on DB level? Or application level?
AM i obliged to achieve this, to loop through all the tickets PER TICKET?
How is performance affected by these look ups?
I did some research and tried to get the tickets alone but haven’t achieved this. I tried it on the level of the DB using:
SELECT ticketID, FROM dbo.Ticket UNION ALL SELECT a.ticketID, FROM dbo.Ticket a INNER JOIN dbo.ticket s ON a.ticketID = s.ticketID SELECT * FROM Ticket where ticketID = 1
While I was testing this, i had the idea that maybe I’m doing this wrong, since maybe, the recursive search is better to be done in my application level (in which i would just need to do a simple select * from ticket in my DB.
Is this true? Would I be needing to do this on application level?
Answer
using a recursive query in SQL Server
Given
With TicketTree(tID, pID)
As
(
Select ticketID, parentID From Ticket Where ticketID = 1
UNION ALL
Select ticketID, parentID from Ticket Inner join TicketTree R On parentID = R.tID
)
select * from TicketTree
gives
or if you query root ticket 4
You can then join with your Activity or whatever you want to do, I just kept it to the minimum need for a recursive query