hello everyone. i'm sure this can be done and i'm sure i can figure it out with enough time, however i'm asking all you experts out there for a quick reference.
i've got a table, 3 columns: ProcessName, Parent, Child
this table houses a process flow for a job (Parent) that starts then finishes and executes the next job (Child) then so forth. each time a job can start and execute another job, a record of the connection is represented.
this table is full of processes and parent jobs executing child jobs. to make it simple, i will use a simple table with simple data:
what i'm trying to do is have a select statement that orders the table by the process name descending, then order the jobs in which they execute. using the child to reference the parent and going backwards, i should be able to return the following result set:
notice that the process name is ordered by descending and that the first record of each process shows that a job fires off another job, then the next row, that job fires off another, and so forth until we hit NULL were the job doesn't fire off any job and stops.
i'm guessing i would have to join the table back into itself. any insights would be greatly appreciated it!