Selecting A Joined Row

If I have these two rows:

Users Table      UserId  |  Username  |  Etc...
                 5       |  John      |

Avatars Table    Id      |  UserId    |  ImageName         |  ImageType
                 1       |  5         |  example.png       |  original
                 2       |  5         |  example_thumb.png |  thumbnail

And I have:

SELECT * FROM Users JOIN Avatars 
ON Users.UserId=Avatars.UserId WHERE Users.UserId = '5'

Do I have to add the additional:

AND Avatars.ImageType = 'thumbnail'

Or is there a way to select all and get the one I want with php (much like an array: $var['something'])??

Or is it possible to distinguish them with mysql using something like the 'AS' c开发者_StackOverflowlause??

Without further qualifying the join with the ImageType condition, you will get multiple rows per user record.

Edit: For example

SELECT u.UserId, u.Username, a.ImageName, a.ImageType
FROM Users u
INNER JOIN Avatars a ON u.UserId = a.UserId
WHERE u.UserId = 5

UserId    Username    ImageName            ImageType
----------------------------------------------------
5         John        example.png          original
5         John        example_thumb.png    thumbnail

Edit #2: If you want both images in one row, use this query

SELECT u.UserId, u.Username,
    orig.ImageName as OriginalImage, thumb.ImageName as ThumbImage
FROM Users u
INNER JOIN Avatars orig ON u.UserId = orig.UserId
    AND orig.ImageType = 'original'
INNER JOIN Avatars thumb ON u.UserId = thumb.UserId
    AND thumb.ImageType = 'thumbnail'
WHERE u.UserId = 5

Note that this will only return rows where both image types are present for the user in Avatars. If you want to get a result irrespective of the existence of avatar images, use left joins.

If you only have two image sizes you could do

SELECT 
    Users.UserId, 
    AvatarsThumbnails.ImageName AS ThumbnailImageName, 
    AvatarsOriginals.ImageName AS OriginalImageName  
FROM Users 
LEFT JOIN Avatars AS AvatarsThumbnails 
    ON Users.UserId=AvatarThumbnails.UserId AND AvatarThumbnails.ImageType='thumbnail' 
LEFT JOIN Avatars As AvatarsOriginals
    ON Users.UserId=AvatarsOriginals.UserId AND AvatarOriginals.ImageType='original'
WHERE Users.UserId = '5' 

SELECT Users.*, O.ImageName as OriginalImage, T.ImageName as Thumbnail
FROM Users
LEFT JOIN Avatars O ON Users.UserId=O.UserId AND T.ImageType = 'original'
LEFT JOIN Avatars T ON Users.UserId=T.UserId and T.ImageType = 'thumbnail'
WHERE Users.UserId = '5'

Use LEFT JOIN so that if either Original or Thumbnail is missing, the other is still retrieved. If both are missing, you still get a record, but with both as NULL.

Even if there was a way to do this in PHP, I would highly suggest letting MySQL do the filtering (by doing 'AND ..."). MySQL was built to do such tasks.