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 d) to basically create a degree of separation. Something like this:
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
Considering Degree0 = 1000001
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.