For my application I need to be able to compare items in a database and output how they're all related. For example:
Ticket #1 Programs:
AA39
FR08
AA05
Ticket #2 Programs:
AA39
FR08
VS01
Ticket #3 Programs:
VS01
WB10
Ticket #4 Programs:
AA01
Now let's say someone wants to do a "spider" (that's what we call it, don't hate me) on Ticket #1. The query should select all the programs from Ticket #1 and then select any tickets that have programs matching Ticket #1. The query finds Ticket #2 has matching programs, so it should then look at all the programs in Ticket #2 and select any other tickets that have programs matching Ticket #2, and so on. So, if you do a spider on Ticket #1, you'd get back:
Ticket #1: aa39, fr08, aa05
Ticket #2: aa39, fr08, vs01
Ticket #3: vs01, wb10
So far I have something conceptual like
Select Ticket, Program as Program1 from DB where ticket = ? and Program = (Select Ticket, Program from DB where Program1 = Program..something
As you can see it kind of falls apart where my brain stops working right. I'm determined it can be done with one query.
Thanks for any help.