CODEWITHSUNDEEP
phpmysql
CruisinCosmo
CruisinCosmo

Reputation: 1157

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' clause??

Upvotes: 1

Views: 73

Answers (4)

Phil
Phil

Reputation: 164946

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.

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

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.

Upvotes: 2

Novikov
Novikov

Reputation: 4489

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'

Upvotes: 2

Mike Lewis
Mike Lewis

Reputation: 64177

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.

Upvotes: 1

Related Questions