SQL Server: How can I get data from junction table that only has IDs?

Question

I have three tables (examples here). Two with data and one that is a junction table to handle many:many relationships.

Users:

ID | UserName
====================
1  | Jeremy Coulson
2  | Someone Else

Repositories:

ID | RepositoryURI
====================
1  | http://something
2  | http://another

RepositoriesUsers:

ID | UserID | RepositoryID
==========================
1  | 1      | 1
2  | 2      | 2

So, in this example, user 1 is associated with repository 1. User 2 is associated with repository 2. I need to now search by Repositories.RepositoryURI and return Users.UserName.

I have this query:

select UserName 
from RepositoriesUsers 
join Users on Users.ID = RepositoriesUsers.UserID
join Repositories on Repositories.RepositoryURI = 'http://another';

But that returns every row in the RepositroriesUsers table.

How can I match data between the junction table with IDs and the other tables with human-friendly text?

Answer 1

You aren't actually giving a correct join condition for your second INNER JOIN. It should be:

SELECT U.UserName 
FROM RepositoriesUsers RU
INNER JOIN Users U 
    ON U.ID = RU.UserID
INNER JOIN Repositories R 
    ON RU.RepositoryID = R.ID
WHERE R.RepositoryURI = 'http://another';

Also, you should try to use table aliases in your queries for clarity.