Azure Database - SQL N:N Query

Question

I'm having a problem with a N:N table in my database. I used the following stackoverflow question to help me in this matter:

How to make SQL many-to-many same-type relationship table

I have a concept of users and these users can maintain a friends list. I created the following 2 tables:

CREATE TABLE [dbo].[Users] (
    [UserId]      UNIQUEIDENTIFIER NOT NULL,
    [UserName]    NVARCHAR (50)    NOT NULL,
    [Password]    NVARCHAR (88)    NOT NULL,
    [E-Mail]      NVARCHAR (100)   NOT NULL,
    [First Name]  NVARCHAR (50)    NOT NULL,
    [Last Name]   NVARCHAR (50)    NOT NULL,
    [Description] NVARCHAR (2000)  NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC)
);
GO

CREATE TABLE [dbo].[Friends]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [UserId] UNIQUEIDENTIFIER NOT NULL, 
    [FriendId] UNIQUEIDENTIFIER NOT NULL
)
GO

I then ran the following alteration query:

ALTER TABLE Friends ADD FOREIGN KEY (userid) REFERENCES Users (userid);
ALTER TABLE Friends ADD FOREIGN KEY (friendid) REFERENCES Users (userid);

I ran the following INSERT query:

INSERT INTO Friends
VALUES (NEWID(), 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82', '6CD2EC21-C32B-4EC3-B221-E36E2A887EA0');

and lastly I ran this query to attempt to retrieve the info:

SELECT *
FROM Users u
   INNER JOIN Friends f ON u.userid = f.userid
   INNER JOIN Friends f ON u.userid = f.friendid
WHERE
   (f.userid = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82' OR f.friendid = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82')
   AND u.userid != 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82';

This presents the following error:

Msg 1011, Level 16, State 1, Line 4
The correlation name 'f' is specified multiple times in a FROM clause.

I understand why I'm getting this error but any changes I make result in no records being retrieved from the DB, can anyone explain what I've done wrong?

Thanks

EDIT 1:

As per first comment:

SELECT *
FROM Users u
   INNER JOIN Friends f1 ON u.userid = f1.userid
   INNER JOIN Friends f2 ON u.userid = f2.friendid
WHERE
   (f1.userid = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82' OR f2.friendid = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82')
   AND u.userid != 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82';

Retrieves nothing.

EDIT 2: Basically I am trying to retrieve all of a given user's friends. Is it possible that my tables are set up incorrectly or my data is invalid? I have 3 users in my Users table, one with UserId of AC30D0BB-3462-4D87-A132-62BE6EBA8E82.

score 2 · Accepted Answer · answered Apr 08 '15 at 16:38

First, a few pointers on modeling. The primary key of a table is needed to enforce uniqueness within a table. UUIDs enforce uniqueness within the known universe. And, as we can all see, makes for horrendous queries. And horrendous results. Quick, search a hundred-row result set for user 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82'. A simple identity field will do just fine

Second, the intersection table doesn't really need a surrogate key. Normally, its key is the combination of the two FKs. Do you have a single query in all of your vast library of queries that searches the intersection table for a surrogate key value? Probably not. You have a user id and you want to search on that to find their friends.

Which leads to the next pointer. You don't ordinarily know or care which side of the friend relationship the user you are searching is on. That is, if user A and user B are friends, you want for see A when searching for friends of B and see B when searching for friends of A. So when creating a friend relationship, write two rows: (A,B) and (B,A). That way, you only have to search one field for all relationships. And if you create the PK on the Friends table as (UserID, FriendID), you will get better results searching always on the UserID field.

Next, the query. If you have user information displayed and your app user want to see a list of friends, you don't need to involve the user table more than once.

select  u.*
from    Friends  f
join    Users    u
    on  u.UserID = f.FriendID
where   f.UserID = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82';

If you want to get user data on the user (got to use another name as the role here is different), then just join the user table a second time.

select  uu.*, fu.*
from    Friends  f
join    Users    fu
    on  fu.UserID = f.FriendID
join    Users    uu
    on  uu.UserID = f.UserID
where   f.UserID = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82';

Wow, thanks for taking the time to answer my question so thoroughly. I'll look to implement a solution like you described tonight. For now have an up-vote for your trouble. Many thanks, Jack — Jack Nutkins, Apr 08 '15 at 16:52

Alex · Answer 2 · 2015-04-07T08:54:02.577

1

You have have to name the tables f1 and f2:

SELECT  *
FROM       Users u1
   INNER JOIN Friends f1 ON u1.userid = f1.userid
   INNER JOIN Users u2 ON u2.userid = f1.FriendId
   INNER JOIN Friends f2 ON u2.userid = f2.friendid
WHERE   ( f2.userid = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82'
    OR f1.friendid = 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82'
   )
   AND u2.userid != 'AC30D0BB-3462-4D87-A132-62BE6EBA8E82';

Does this query meet your requirements ? I have made some assumptions on what logic you are trying to implement as you did not detail this in your original post.

edited Apr 07 '15 at 08:54

answered Apr 07 '15 at 08:39

Alex

21,273
10
61
73

Hi Jaco, see my edit. If I use your query exactly I get 'Incorrect syntax near the keyword 'INNER'.' relating to the AND. Thanks for your time. – Jack Nutkins Apr 07 '15 at 08:42
I made further corrections to your original query. Is the above query closer to what you are after ? – Alex Apr 07 '15 at 08:55

Azure Database - SQL N:N Query

2 Answers2