• 0

[SQL] Query for Friends of Friends?


Question

So for a database assignment I have to do, we have to make a mockup social network.

I have a table for various members of the network (using email as the primary key), and another table called 'friends' which has the columns 'email1' and 'email2'. Each friendship on this table is only listed once, and in no particular order.

How can I make a query that will output a list of everyone who shares a mutual friend with everyone else?

The query shouldn't be relative to any specific user; it should list every friend-of-a-friend for every user.

So, as an example:

Email1				 Email2
a@a.com				b@b.com
a@a.com				c@c.com
b@b.com				d@d.com
d@d.com				e@e.com

So, I would want it to return B and C, who have A as a mutual friend. It would also return B and E, who have D as a mutual friend, as well as A and D, who have B as a mutual friend.

Thanks so much,

David

Link to comment
Share on other sites

14 answers to this question

Recommended Posts

  • 0

it's the requirement of the assignment. We're not going for speed here, just a functional model. My professor told us that we're supposed to be using Facebook as a model, and he said that, for our purposes, the email address can act as the primary key because it's unique to each user (seeing as it's each person's username for the site).

Link to comment
Share on other sites

  • 0
it's the requirement of the assignment. We're not going for speed here, just a functional model. My professor told us that we're supposed to be using Facebook as a model, and he said that, for our purposes, the email address can act as the primary key because it's unique to each user (seeing as it's each person's username for the site).

Yeah, that's a horrible primary key. Need to change your email address? Have fun updating all those tables, and records.

Facebook doesn't use email as a primary key. It's a unique constraint for sure because you login to the site with it, but each user has a unique id that doesn't change.

I know this is a fake project, and model, but that's just awful. Might as well teach students how to write code that's prone to sql injection.

Link to comment
Share on other sites

  • 0

sbauer -- valid point, and i am aware of the difference between a unique constraint and a primary key; i am aware that in real life circumstances this wouldn't be particularly practical. but yeah, like you said, it's a fake project so it doesn't affect too much in the scheme of things.

any ideas for a solution to the actual question at hand, though?

Link to comment
Share on other sites

  • 0

Personally I'd do it this way:

User table: ID (PK), Email

Friends Table: ID (PK), UserID (FK - linked to ID on User table), FriendID (FK - linked to ID on User table.)

So one user can have many friends.

So for example, you select a user, then would then do:

SELECT FriendID FROM Friends WHERE UserID = <UserID>

which would return a list of user ID's which are friends of the User and then for each of the rows in that query, find the user using that User ID.

Similar to yours but with a proper Primary Key :-P

GE

Link to comment
Share on other sites

  • 0

God not even our SQL course is that bad... :|

Email as primary key, I am actually shocked at that, why each teach databases, it's like comparing << spam >> and hugeurl.com and choosing hugeurl.com because 'bigger is better'...

Link to comment
Share on other sites

  • 0

thanks for the response garethevans1986.

however, that would only give a list of mutual friends between yourself and one other person, no? how would you get it to give you a list of mutual friends between yourself and every friend on your list?

Link to comment
Share on other sites

  • 0

Wow guys cry about the PK more...

This would do the job (self-join):

SELECT friends.Email1, friendsOfFriends.Email2 FROM friends INNER JOIN friends AS friendsOfFriends ON (friends.Email2 = friendsOfFriends.Email1)

If you wanted something practical, you might also want to exclude the immediate friends from friends of friends (ex. if B was friends with C, and A is friends B and C, then C is not really a friend of a friend of A):

SELECT friends.Email1, friendsOfFriends.Email2 FROM friends INNER JOIN friends AS friendsOfFriends ON (friends.Email2 = friendsOfFriends.Email1 AND friendsOfFriends.Email2 NOT IN (SELECT Email2 FROM friends b WHERE b.Email1 = friends.Email1))

Link to comment
Share on other sites

  • 0

thanks for responding jon; i'm getting an error when i'm trying to run your query through access.

i'm thinking the error may have something to do with where you first put in that 'b' in the second line?

Link to comment
Share on other sites

  • 0

Sorry, I wrote it for MySQL where the "AS" for table aliases is optional.

SELECT friends.Email1, friendsOfFriends.Email2 FROM friends INNER JOIN friends AS friendsOfFriends ON (friends.Email2 = friendsOfFriends.Email1 AND friendsOfFriends.Email2 NOT IN (SELECT Email2 FROM friends AS b WHERE b.Email1 = friends.Email1))

Also, Access has issues with the IN () clause, so it might still not work.

Link to comment
Share on other sites

  • 0

thanks again, Jon. i actually managed to tweak your first query to give the same result as your second one.

i was still getting errors with both of the ones you sent me. what i did to fix it was change the AND after the 'on' to the "WHERE" clause. after having changed that, though, it seems to be working.

thanks jon, you're the man.

here's my current result:

SELECT DISTINCT f.email1, f2.email2

FROM friends as f INNER JOIN friends as f2 ON (f.email2 = f2.email1)

WHERE f2.email2 NOT IN (SELECT email2 FROM friends as b WHERE b.email1=f.email1)

i added the distinct because if someone shares 2 mutual friends with me, the row would come up twice in the table.

Link to comment
Share on other sites

  • 0

I thought Neowin has a policy... we don't do your homework. Now had you actually posted a query and needed assistance in resolving an error, that would be acceptable. However, you didn't even bother attempting. What do you plan to do during a job interview and someone asks a question as simple as your assignment?

Link to comment
Share on other sites

  • 0

Noted, but the thing is, seeing as I played a (somewhat) active part in the process of getting the answer, I now understand the logic behind the conclusion.

And next time, maybe make your question a little less patronizing? I didn't come here with malicious intent.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.