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?

i know you have some good answers below, but it might have made more sense to you, if you started at the table you were filtering on and worked your way down.. `from Repositories r join RepositoriesUsers ru on r.Id = ru.RepositoryID join Users u on ru.UserId = u.Id where r.RepositoryURI = 'http://another` — JamieD77, Oct 05 '15 at 15:40

score 7 · Accepted Answer · answered Oct 05 '15 at 15:30

7

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.

answered Oct 05 '15 at 15:30

Lamak

69,480
12
108
116

Perfect. Thank you. I see what's up now. I will accept your answer in seven minutes :) – mrcoulson Oct 05 '15 at 15:33

score 1 · Answer 2 · answered Oct 05 '15 at 15:33

1

I think a simple correction is needed to your join:

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

answered Oct 05 '15 at 15:33

Pankaj Manek

21
2

This is correct but this answer has already been submitted by Lamak. – Sean Lange Oct 05 '15 at 15:34

score 0 · Answer 3 · answered Oct 05 '15 at 15:32

0

Please change your join condition for Repositories table and add a WHERE clause as follows

select *
from RepositoriesUsers 
join Users on Users.ID = RepositoriesUsers.UserID
join Repositories on Repositories.ID = RepositoriesUsers.RepositoryID
where RepositoryURI = 'http://another'

answered Oct 05 '15 at 15:32

Eralper

6,461
2
21
27

This is correct but this answer has already been submitted by Lamak. – Sean Lange Oct 05 '15 at 15:36

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

3 Answers3