Query to add 2 queries?

[helamin]

Hi there,

I have a database table that has entries of users and their friends. so this tblFriendUser has a column called UserName and another column called FriendUserName.
I am trying to get a list of friends for that particular user. Note that if User1 initiated the friend request, he will be listed as UserName and his friend as FriendUserName, but if his friend initiated the friend request, it will be vice versa: him being the FriendUserName and his friend the UserName. So I want the following 2 queries added to one query and returning 2 columns: UserFriendID & UserName, is that possible? Is my design bad? Any suggestions would help! Thanks a lot!


SELECT UserFriendID, UserName
FROM tblUserFriends
WHERE (UserName = @UserName);

SELECT UserFriendID, FriendUserName AS UserName
FROM tblUserFriends
WHERE (FriendUserName= @UserName);

[nosepicker]

Can a user have more than one friend? If not, then this design is passable, although not necessarily ideal and probably not normalized. If a user can have multiple friends, then things will get messy quickly with this design. If you have other information associated with the users, it would probably be better to have a separate table for all users who could possibly initiate this request, and have that join to this table with the friend relationships. However, I couldn't really recommend an ideal design without knowing the entire scope of your business logic.

[helamin]

yes, it will be a social networking site, with one user being friends with a lot of users. Can anyone please point me in the right direction as far as the design is concerned? I appreciate it.

[nosepicker]

Again, without knowing what your business requirements are, I can't give you a comprehensive example, but here is a sort of stripped-down one:

CREATE TABLE Users (
UserID int NOT NULL,
UserName varchar(100) NOT NULL,
Birthdate datetime NULL,
Address varchar(100) NULL,
Phone varchar(15) NULL)

CREATE TABLE UserFriends (
UserID int NOT NULL,
FriendID int NOT NULL)


INSERT INTO Users VALUES(1, 'Joey', '1970-01-01', 'Address1', '555-1111')
INSERT INTO Users VALUES(2, 'Chandler', '1970-02-01', 'Address2', '555-2222')
INSERT INTO Users VALUES(3, 'Ross', '1970-03-01', 'Address3', '555-3333')
INSERT INTO Users VALUES(4, 'Monica', '1970-04-01', 'Address4', '555-4444')
INSERT INTO Users VALUES(5, 'Phoebe', '1970-05-01', 'Address5', '555-5555')
INSERT INTO Users VALUES(6, 'Rachel', '1970-06-01', 'Address6', '555-6666')

INSERT INTO UserFriends VALUES(1, 2)
INSERT INTO UserFriends VALUES(1, 5)
INSERT INTO UserFriends VALUES(1, 6)

INSERT INTO UserFriends VALUES(2, 1)
INSERT INTO UserFriends VALUES(2, 3)
INSERT INTO UserFriends VALUES(2, 4)
INSERT INTO UserFriends VALUES(2, 5)

INSERT INTO UserFriends VALUES(5, 1)
INSERT INTO UserFriends VALUES(5, 2)
INSERT INTO UserFriends VALUES(5, 3)
INSERT INTO UserFriends VALUES(5, 4)
INSERT INTO UserFriends VALUES(5, 5)


The "Users" table will contain just the information relevant to each individual user. The "UserFriends" table will show the relationships. You can make "UserID" a primary key in Users, and the combination of UserID and FriendID can make up a primary key in UserFriends. If you want to create a separate, single-column primary key for UserFriends, that's fine. There should be a foreign key relationship between these two tables via the UserID columns. There are certainly other ways to do this - this is just a basic example off the top of my head.

[helamin]

and thats how i have mine setup. the problem is that when UserId1 is friends with FriendId2, then in my table, I will add one entry:
INSERT INTO UserFriends VALUES(1, 2)
Now when I query the database to get friends of UserId1, then 2 will show up. (SELECT UserFriends FROM UserFriends WHERE UserId=1) But when I query to get friends of UserId2, 1 will not show up. (SELECT UserFriends FROM UserFriends WHERE UserId=2)
Should I add 2 entries in the table when 2 people become friends?
INSERT INTO UserFriends VALUES(1, 2)
INSERT INTO UserFriends VALUES(2, 1)
That would solve my problem, but I was wondering if there's another way to do it?
Thanks!

[nosepicker]

Yes, that's what I was trying to show with my sample data, although I didn't explicitly say that in my last post. I think it would make things easier if you created 2 records for a friendship that involves 2 valid users. I'm sure there are other ways to do this, but that seems to be the most logical way to me.

[helamin]

sorry for the stretch, but how would I search for friends?
i want to select all the users that have been approved. so when user1 makes a request to friend user2, i create one entry in the table. when user2 accepts the friend request, i create another entry.
so when i want to select a user's friends, i have to select all users that have been approved, so select (user1, user2) pair only if another (user2, user1) pair exists?
sounds too complex to me, when it could be easier?

[nosepicker]

Ok, if there has to be some sort of approval process, then it might be easier to add another column in the UserFriends table to indicate if the relationship has been approved. Otherwise, you can do a couple of self-joins. For example, if you take the sample data I posted, and you wanted to search for Joey's friends:

SELECT U1.UserName, U1.UserID, U2.UserID, U2.UserName
FROM Users AS U1
JOIN UserFriends AS UF1
ON U1.UserID = UF1.UserID
JOIN UserFriends AS UF2
ON UF1.FriendID = UF2.UserID
AND UF1.UserID = UF2.FriendID
JOIN Users AS U2
ON UF1.FriendID = U2.UserID
WHERE U1.UserID = 1

[helamin]

you're the best nosepicker! Thanks a lot for your time.