CODEWITHSUNDEEP
sqlsql-serverdatabase
Maikkeyy
Maikkeyy

Reputation: 1047

How to select a field which is a subquery, that returns more than 1 value?

I have two tables: an User table and a [user_friend] table. In the [user_friend] table you can see which user is friend with a certain user.

The User-table looks like this:

 [User_ID], [Username], [Password]
      1       Mikeyy1     666je
      2       misterG     8kdkd

The friends-table looks like this: (where Friend_ID has a foreign key to the User_ID in User) 

[User_ID] [Friend_ID]
   1           3
   1           6

I want to query the following: show all the names of users with their friend's names.

So far, i have this:

SELECT u.Username, f2.Friend_ID
FROM [User] u JOIN [user_friend] f2
ON u.User_ID = f2.User_ID

which returns all the users username and the friend_ID's of their friends, but i also want their names, so i thought this:

SELECT u1.Username, f2.Friend_ID, (SELECT u.Username FROM [User] u 
JOIN [user_friend] f ON u.User_ID = f.friend_ID) AS "Friend"
FROM [User] u1 JOIN [user_friend] f2
ON u1.User_ID = f2.User_ID

but that gives the error that the subquery is returning more than 1 value, how can i fix this?

Thanks you in advance!

Upvotes: 1

Views: 57

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

@Felix Pamittan Is the usual way to solve it. But just for completeness, your original query should be

SELECT u1.Username, 
       f2.Friend_ID, 
       (SELECT u2.Username 
        FROM [User] u2 
        WHERE u2.User_ID = uf.friend_ID) AS "Friend"
FROM [User] u1 
JOIN [user_friend] uf
  ON u1.User_ID = uf.User_ID

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

You need to add another JOIN:

SELECT
    u.Username,
    u2.Username
FROM [User] u
INNER JOIN [User_Friend] uf
    ON uf.User_ID = u.User_ID
INNER JOIN [User] u2
    ON u2.User_ID = uf.Friend_ID

Upvotes: 2

Related Questions