7 Degrees of Separation
Good morning world!
I am sure this is a simple problem, but unfortunately my prime experience in SQL is coupled with .asp or .php which gives (me) much more flexibility when it comes to re-using fields. Whatever ... here is the basic problem that I am sure is simple for my friends out there in internetland. I need to create a query (in SSRS (connecting to Oracle dB)) to basically create a degree of separation. Something like this:
Considering Degree0 = 1000001
1000001 Kevin Bacon
1000002 Brad Pitt
1000003 George Clooney
1000004 Edward Norton
1000005 John Lithgow
RRecordID Person1 Person2 Relation
10000001 1000001 1000002 Sleepers
10000002 1000002 1000003 Oceans
10000003 1000005 1000001 Footloose
10000004 1000004 1000002 Fight Club
Or ... whatever ... something like that
My query needs to SELECT (the opposite) Person1/Person2 AS Degree1, Relation Where Person1/Person2 = Degree1
then SELECT DISTINCT (the opposite) Person1/Person2 AS Degree2, Relation Where Person2/Person1 = the field selected in the prior query (without including Degree0 or Degree1)
AND So on and so forth ...
Hopefully this is sufficiently explaining what I am trying to do, but it is basically a seven degrees of separation query where the data may exist in one of two columns. I have a working solution right now, but instead of embarassing myself by showing my sloppy code, I thought I would have an expert show me the EASY way. Thank you.
My first thought is that I'd have 3 tables: NameTable, MovieTable, and ActorToMovieTable, the latter giving you a normalized way to have 0 to n people associated with any given movie. However, that's the easy part: I'd have to really think hard about how to solve the relationship resolution question, but my gut instinct right now is that it would mainly center on that ActorToMovieTable table, once you've selected the 2 name IDs you want to connect.
Okay, so not a lot of help, but an interesting puzzle which maybe will tempt me to think about it more when I have spare time (whatever that is).
Thanks for the response. Unfortunately, I do not have control over the tables ... for if I did, it certainly would be set-up differently. The way I currently have it accomplished is by searching InStr(Person1 + 'x' + Person2, Degree0), but then I have to do some manipulation which makes my head spin. My current code pretty much stops after the 2nd degree because of my "creative" logic. I truly thought this would be a simple problem that I was overthinking, glad to see that it is not THAT simple (for the sake of my ego), sad because I still don't have a "good" query. Thanks again.