# 7 Degrees of Separation

Printable View

• 04-04-2013, 09:33 AM
gift2women
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:
Code:

```NameTable PRecordID Name 1000001 Kevin Bacon 1000002 Brad Pitt 1000003 George Clooney 1000004 Edward Norton 1000005 John Lithgow RelationTable 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.
• 04-04-2013, 12:12 PM
NogDog
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).
• 04-04-2013, 12:19 PM
gift2women
NogDog,
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.

## X vBulletin 4.2.2 Debug Information

• Page Generation 0.07345 seconds
• Memory Usage 2,349KB
• Queries Executed 11 (?)
More Information
Template Usage (20):
• (1)ad_footer_end
• (1)ad_footer_start
• (1)ad_global_above_footer
• (1)ad_global_below_navbar
• (1)ad_global_header1
• (1)ad_global_header2
• (1)ad_navbar_below
• (1)bbcode_code_printable
• (1)footer
• (1)gobutton
• (1)header
• (1)headinclude
• (1)headinclude_bottom
• (1)navbar_moderation
• (1)navbar_noticebit
• (2)option
• (1)printthread
• (3)printthreadbit
• (1)spacer_close
• (1)spacer_open

Phrase Groups Available (3):
• global
• postbit
• showthread
Included Files (19):
• ./printthread.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/config.php
• ./includes/functions.php
• ./includes/functions_navigation.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./includes/functions_facebook.php
• ./includes/functions_calendar.php
• ./includes/class_bbcode_alt.php
• ./includes/class_bbcode.php
• ./includes/functions_bigthree.php
• ./includes/functions_notice.php

Hooks Called (41):
• init_startup
• init_startup_session_setup_start
• database_pre_fetch_array
• database_post_fetch_array
• init_startup_session_setup_complete
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_threadinfo_query
• fetch_threadinfo
• fetch_foruminfo
• load_show_variables
• load_forum_show_variables
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• printthread_start
• bbcode_fetch_tags
• bbcode_create
• bbcode_parse_start
• cache_templates
• cache_templates_process
• template_register_var
• template_render_output
• fetch_template_start
• fetch_template_complete
• parse_templates
• fetch_musername
• notices_check_start
• notices_noticebit
• friendlyurl_resolve_class
• friendlyurl_clean_fragment
• friendlyurl_geturl
• process_templates_complete
• bbcode_parse_complete_precache
• bbcode_parse_complete
• printthread_post
• printthread_complete
• page_templates