SQL Query with Multiple table for conditions

Question

I have an issue with a table I am trying to produce in Visual Studio 2010. I am not quite sure how to formulate my SQL query that forms my datasource.

Basically I have two tables. One of them contains picture filenames and has two columns, picturefile (PK) and username. The second table contains three username columns: the first colum defines a username,the second one of the username's friends so as to form friend relationships and the third column is a RelationshipID.

I would like to have my query produce a table that shows all the pictures from the pictures table that are owned by the current user's friends. i have the parameter @currentuser which can be used So this would be a Your Friend's Pictures Table.

The attempt I had, bear in mind I am very much a beginner, is this:

SELECT picturefile, username
FROM     pictures_table1
WHERE (username = (SELECT User2 FROM friendslist_table1 WHERE     friendslist_table1.Username1 = @currentuser AND friendslist.Username2 <> @currentuser))

It gives an error saying the subquery cannot return mulitple values.

Thank you.

Answer 1

SELECT f.friendname,p.picturefile from pictures_table1 p
INNER JOIN friendslist_table1 f
ON p.username = f.username
WHERE f.username = @currentuser

Answer 2

Please look at JOIN function, it does exactly what you want.