Results 1 to 3 of 3

Thread: 7 Degrees of Separation

  1. #1
    Join Date
    Jan 2008
    Surprise, AZ

    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:
    PRecordID Name 
    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.

  2. #2
    Join Date
    Aug 2004
    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).
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    Join Date
    Jan 2008
    Surprise, AZ
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center