SQL Server: How to Select First, Second, and Third Degree Contacts

Question

I am working on a social website project, and I need to list the contacts "first, second and third degree contacts" of my contacts. I am using SQL Server And C #

Suppose the contact table is as follows:

For contact of the first degree:

• If gulsah , then my first degree contacts are burak,sennur

The query that I use to select this:

 SELECT contact_2 FROM Contacts_Table WHERE contact_1 like 'gulsah' 

For a second degree contact:

If gulsah is me again, then my contacts are second degree: mali

What prevents you from choosing the contacts of my contacts, which are not my first contact person.

I can choose reciprocal contacts, but I think this is the wrong approach.

For example, to select the mutual contacts of me ( gulsah ) and burak :

 SELECT contact_1 FROM (SELECT * FROM Contact_Test WHERE contact_2 like 'burak') a INNER JOIN (SELECT contact_1 FROM Contact_Test WHERE (contact_2 = 'gulsah')) b ON a.contact_1 = b.contact_1 

This query works, but, as I said, this is not the right approach for this to work.

For contact of the third degree:

If gulsah is me again, then my third degree contacts are_ mehmet,ahmet

I need to select the contacts of my contacts, which are not my contacts of the first and second degree :)

Here is a post from Linkedin explaining the contact level.

Thanks for answers.

Answer 1

Here is my approach:

• Add my contact to a special contact list.

• For each contact in the assembled list as Contact_1 the contact table, add its corresponding Contact_2 if this contact is no longer in the assembled list.

• Repeat step # 2 the number of times, which is the number of the target degree minus one.

• Repeat the query in step # 2 again, but this time just return the result set (do not add rows to the compiled list).

script:

 DECLARE @MyContact varchar(50), @DegreeNumber int; SET @MyContact = 'gulsah'; SET @DegreeNumber = 3; DECLARE @CollectedContacts TABLE (Contact varchar(50)); INSERT INTO @CollectedContacts (Contact) VALUES (@MyContact); WHILE @DegreeNumber > 1 BEGIN INSERT INTO @CollectedContacts (Contact) SELECT ct.Contact_2 FROM Contacts_Table ct INNER JOIN @CollectedContacts cc ON ct.Contact_1 = cc.Contact LEFT JOIN @CollectedContacts cc2 ON ct.Contact_2 = cc2.Contact WHERE cc2.Contact IS NULL; SET @DegreeNumber = @DegreeNumber - 1; END; SELECT ct.Contact_2 FROM Contacts_Table ct INNER JOIN @CollectedContacts cc ON ct.Contact_1 = cc.Contact LEFT JOIN @CollectedContacts cc2 ON ct.Contact_2 = cc2.Contact WHERE cc2.Contact IS NULL; 

As you can see, both the degree number and my contact are parameterizable. I use the varchar type for contacts, but of course, it can be easily replaced with int if necessary.

Answer 2

What prevents you from choosing the contacts of my contacts, which are not my first contact person.

You can use the EXCEPT statement.

First degree contacts:

 SELECT contact_2 FROM contact WHERE contact_1 = 'gulsah' 

Contacts of the second degree, which are not contacts of the first degree:

 SELECT contactB.contact_2 FROM contact AS contactB INNER JOIN contact AS contactA ON contactA.contact_2=contactB.contact_1 WHERE contactA.contact_1 = 'gulsah' EXCEPT SELECT contact_2 FROM contact WHERE contact_1 = 'gulsah' 

EXCEPT tells the SQL server to return all results from the first SELECT that DO NOT appear in the second SELECT .

For contacts of the third degree (which are not contacts of the first or second degree):

 SELECT contactC.contact_2 FROM contact AS contactC INNER JOIN contact AS contactB ON contactB.contact_2=contactC.contact_1 INNER JOIN contact AS contactA ON contactA.contact_2=contactB.contact_1 WHERE contactA.contact_1 = 'gulsah' EXCEPT ( SELECT contact_2 FROM contact WHERE contact_1 = 'gulsah' UNION SELECT contactB.contact_2 FROM contact AS contactB INNER JOIN contact AS contactA ON contactA.contact_2=contactB.contact_1 WHERE contactA.contact_1 = 'gulsah' ) 

I do not have high expectations for performance, but, of course, you will need to see for yourself.

---

As a note:

 I can select mutual contacts but I guess it is not the right approach. 

Use INTERSECT for this.

Answer 3

maybe this helps: http://techportal.ibuildings.com/2009/09/07/graphs-in-the-database-sql-meets-social-networks/