0

I work in a ticketing system that exports data to a reporting DB using the company's version of SQL (similar to Postgre, though). I'm looking for a way to create a statement that pulls rows that are aggregates of related tickets. I've been having trouble searching StackOverflow or Google for this answer or anything similar.

Tickets can relate to each other in one direction. If ticket A is linked to the newly created ticket B, a row appears in a related items table as

First ticket    Second ticket
------------    -------------
Ticket A        Ticket B

Sometimes ticket B will get related to ticket C and ticket C is related to ticket D and so on and so on. 

First ticket    Second ticket
------------    -------------
Ticket A        Ticket B
Ticket B        Ticket C
Ticket C        Ticket D
Ticket D        Ticket E
Ticket E        Ticket F

I can see that tickets A, B, C, D, E, and F are related but I don't know how to pull that data to show that. I want to look at a string of all related tickets and determine statistics on this chain. I've tried to self-join on this table 5 times but what if the relation goes 6, 7 or n tickets deep?

I suppose what I would want is a statement that could look at any ticket in this related chain and produce a row that looks like

Ticket A, Ticket B, Ticket C, Ticket D, Ticket E, Ticket F

Thanks

Mike Rrrrr.
  • 1
  • How about the situation of circular reference? Suppose you built an SQL or something else how do you want to handle such case like, for example, the coexistence of two records `("Ticket A", "Ticket B")` and `("Ticket B", "Ticket A")`? – Philip Tzou Jun 17 '17 at 00:52
  • 3
    You can use a recursive CTE to track the links to any depth. – rd_nielsen Jun 17 '17 at 01:46
  • [Fetching all parents in simple way with PostgreSQL](https://stackoverflow.com/q/32649731/1995738) – klin Jun 17 '17 at 12:58
  • What you want is a function of the "transitive closure" of tthe relationship/association represented by your table. https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – philipxy Jun 19 '17 at 12:00
  • @PhilipTzou the system automatically prevents circular reference so while I feel normally I would need to account for this, in this case, I won't. Of course, I also believe one should put up guardrails for the just-in-case scenario.. – Mike Rrrrr. Jun 21 '17 at 07:37
  • @klin I had a feeling this would come up. What annoys me about the internal SQL style used is that while I can use a WITH clause, I can't use WITH RECURSIVE. It's sounding like my lack of use of WITH RECURSIVE will hurt my ability to pull in this data – Mike Rrrrr. Jun 21 '17 at 07:37
  • The only alternative to get a dynamic solution is to use recursive functions. You cannot use `with recursive` because of an older Postgres version? – klin Jun 21 '17 at 12:18
  • @klin It's an SQL system used internall at my company that in my limited experience (Oracle, Postgre, Dremel) seems most similar to Postgre. Internal documentation states that while the WITH clause is available, WITH RECURSIVE is not supported at this time. I may need to search for a solution internally. Thanks for your time, honestly. – Mike Rrrrr. Jun 22 '17 at 20:15

0 Answers0