CODEWITHSUNDEEP
sql-server-2008t-sql
TurnItUp
TurnItUp

Reputation: 5

How to get multi columns via subquery?

I want to get members and their photos. Every member has 2 photos. (I am not talking about profile image)

There are 2 tables named as Members and MemberPhotos.

Here is my query which doesn't work(expectedly):

SELECT
M.Name as MemberName,
M.LastName as MemberLastName,

(
    SELECT
        TOP 1
        MP.PhotoName
    FROM
        MemberPhotos MP
    WHERE
        MP.MemberID = M.ID
        AND
        MP.IsFirst = 1
) as MemberFirstPhoto,

(
    SELECT
        TOP 1
        MP.PhotoName
    FROM
        MemberPhotos MP
    WHERE
        MP.MemberID = M.ID
        AND
        MP.IsFirst = 0
) as MemberSecondPhoto,

FROM
    Members M

Maybe somebody going to say that I should use inner join instead, I don't want to use inner join, if I use it I get data multiple like:

Name       Surname       PhotoName

Bill       Gates         bill.png
Bill       Gates         bill2.png
Steve      Jobs          steve.jpg
Steve      Jobs          steve2.jpg

What do you recommend me about query?

Thanks.

EDIT: Here is the output I want to get:

Name       Surname       FirstPhoto       SecondPhoto

Bill       Gates         bill.png         bill2.png
Steve      Jobs          steve.jpg        steve2.png

Upvotes: 0

Views: 89

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

The only issue with your example query is that you have an extra comma after

as MemberSecondPhoto

If you remove this it works fine.

SQL Fiddle with demo.

However, while that query is working now, because you know that each member only has two photos, you can use a much simpler query:

SELECT
  M.Name as MemberName,
  M.LastName as MemberLastName,
  MPF.PhotoName as MemberFirstPhoto,
  MPS.PhotoName as MemberSecondPhoto
FROM Members M
  LEFT JOIN MemberPhotos MPF ON M.ID = MPF.MemberID AND MPF.IsFirst = 1
  LEFT JOIN MemberPhotos MPS ON M.ID = MPS.MemberID AND MPS.IsFirst = 0

SQL Fiddle with demo.

Upvotes: 1

Related Questions