I found an interesting article (Deconstructing Myspace.com Part 1 - Social Networking Database, btw there is not a Part 2) and after discovering that the basic structure behind these social networking sites is very simple, I wanted to experiment with it. I am having some issues with the queries suggested in the article. Here is what I did, please let me know if you see any errors.

CREATE TABLE IF NOT EXISTS Accounts (
ID varchar(255) NOT NULL default '',
DisplayName varchar(25) default NULL,
FirstName varchar(25) default NULL,
LastName varchar(25) default NULL,
Username varchar(25) default NULL,
Password varchar(255) default NULL,
PRIMARY KEY  (ID)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Friends (
AccountID varchar(255) NOT NULL default '',
FriendID varchar(255) NOT NULL default '',
PRIMARY KEY  (AccountID,FriendID),
CONSTRAINT FOREIGN KEY (AccountID) REFERENCES Accounts (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (FriendID) REFERENCES Accounts (ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

I was able to create two tables using similar code as above. I then wanted to play with the suggested queries. So I inserted some info like this.

INSERT INTO Accounts (ID) VALUES ('Person1');
INSERT INTO Accounts (ID) VALUES ('Person2');
INSERT INTO Accounts (ID) VALUES ('Person3');

Next I made Person1 and Person2 friends and Person3 request a friendship of Person1.

INSERT INTO Friends (AccountID,FriendID) VALUES ('Person1', 'Person2');
INSERT INTO Friends (AccountID,FriendID) VALUES ('Person2', 'Person1');
INSERT INTO Friends (AccountID,FriendID) VALUES ('Person3', 'Person1');

Following the first two inserts in the Friends table above, it is my understanding that for two people to be friends, there must be two entries in the table. The first has personA as the AccountID and personB as the FriendID, then the second is reversed with personB as the AccountID and personA as the FriendID. However, as above, there is not a friendship between Person1 and Person 3 yet. Is this correct?

So I tried to run some of the sample queries (i.e. Listing 2) and they failed. What is wrong with the following query? Apparently it should return all the people who have requested friendship with Person1 and are not friends yet. In this case Person3 should be returned I believe.

SELECT DISTINCT a.* FROM Accounts a INNER JOIN Friends f ON a.ID = f.AccountID WHERE f.FriendID = 'Person1'

Thanks for the help!

Anyone know why the select query fails?

Try

SELECT DISTINCT * FROM Accounts NATURAL JOIN Friends ON Accounts.ID = Friends.AccountID WHERE Friends.FriendID = 'Person1'

Inner join will give you the cartesian product of the two tables, and I don't think that is what you want. Also I cleaned up your syntax a bit.

By the way, don't use SELECT * unless you actually need every column. Specify the columns you need (ie SELECT Accounts.username , Friends.AccountID etc)

Hi NullPointer,
Thank you for the reply. I tried your query but it throws the following error:

Error

SQL query:
SELECT DISTINCT *
FROM Accounts
NATURAL JOIN Friends ON Accounts.ID = Friends.AccountID
WHERE Friends.FriendID = 'Person1'
LIMIT 0 , 30
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON Accounts.ID = Friends.AccountID WHERE Friends.FriendID = 'Person1'
LIMIT 0, ' at line 1

My mistake. Use a left join in place of the natural join and that should work.

I used left join, but still get the wrong results. It returned Person2 and Person3 when only Person3 needs to be returned.

The query I used is

SELECT DISTINCT * FROM Accounts LEFT JOIN Friends ON Accounts.ID = Friends.AccountID WHERE Friends.FriendID = 'Person1'

I'm actually kind of thinking this is not possible. I think the author of the article may not have tested his ideas.