Help building a two level query

[pfiroz]

Hi,
I'm stuck on what seems would be fairly easy but its not quite working right.
Basically, I just want to do a 2 level query. I have a main table with alot of peoples names (first, middle, last). I have an affiliations table with people who are affiliated to the people in the main table. These affiliations are associated with the key from the main table called 'Person_ID'.
Ok so now I want to also find out who all the affiliation people are associated with in the main table.
The reason for this is that we are going to do a social network analysis.
So we would (for example) be able to see a person, say billy bob, that is associated to sue ann, and Rob. and then see that sue ann is also associated to kevin, george, and mike from the main table. etc etc...
This query/table that I need to build needs to have all the information so it can be fed into a analysis software. Whats the best way to build a query to have that information. I guess the query would have multiple entries for a person in the main table then WithIN those entries there would be multiple entries for a particular affiliation which would be because that affiliation is associated to multiple people from the Main table.
I hope that makes sense !
Appreciate any help!

 

[evanscamman]

This is a bit over my head, but it sounds like you are looking for a recursive query. Try searching for "recursive employee". There are many helpful articles that deal with similar scenarios.

Evan

 

[WIS]

Hi,
I'm stuck on what seems would be fairly easy but its not quite working right.
Basically, I just want to do a 2 level query. I have a main table with alot of peoples names (first, middle, last). I have an affiliations table with people who are affiliated to the people in the main table. These affiliations are associated with the key from the main table called 'Person_ID'.
Ok so now I want to also find out who all the affiliation people are associated with in the main table.
The reason for this is that we are going to do a social network analysis.
So we would (for example) be able to see a person, say billy bob, that is associated to sue ann, and Rob. and then see that sue ann is also associated to kevin, george, and mike from the main table. etc etc...
This query/table that I need to build needs to have all the information so it can be fed into a analysis software. Whats the best way to build a query to have that information. I guess the query would have multiple entries for a person in the main table then WithIN those entries there would be multiple entries for a particular affiliation which would be because that affiliation is associated to multiple people from the Main table.
I hope that makes sense !
Appreciate any help!

Are you saying that 1 person from the main tbl can have several affiliates and one affiliate can have several persons. eg like 1 doctor can have several patients and 1 patient can have several doctors.

If so, you have a many to many relationship between the 2 tbls and you will need a 3rd tbl with 2 flds, Person_ID (from main tbl) and Affiliate_ID (from affiliates tbl).