select count and random field in left join

[thuijzer]

Hi All,

 

Ive got two tables for a photo-gallery. One album-table and one images table.

Now I want to select my albums and the count of images inside an album and a radom image from that same album.

But I can't get it to work. My query so far:

 

SELECT
album.albumId AS albumId,
album.title AS title,
COUNT(albumImages.albumImageId) AS albumImageId,
albumImages.url AS url
FROM album
LEFT JOIN albumImages ON ( albumImages.albumId = album.albumId)
WHERE album.organisationId = 1
AND album.languageId = 1
GROUP BY album.albumId

 

I only get one 'albumImages.url' but I cannot order it in any way. I would like it to be a random album image.

Can someone please help me?

 

MySQL 4.1.12-nt

[Illusion]

http://www.phpfreaks.com/forums/index.php/topic,125759.0.html

[thuijzer]

http://www.phpfreaks.com/forums/index.php/topic,125759.0.html

Thank you. I already studied that link but it didn't help me very much.

 

The query below will work without errors but the COUNT is wrong. It will return 16 instead of 4.

And it won't return a random picture either. It is always returning the same picture.

SELECT
album.albumId AS albumId,
album.title AS title,
COUNT(albumImages1.albumImageId) AS albumImageId,
albumImages2.url AS url
FROM album
LEFT JOIN albumImages AS albumImages1 ON (albumImages1.albumId = album.albumId)
LEFT JOIN albumImages AS albumImages2 ON
(
albumImages2.albumId = album.albumId
AND albumImages2.albumImageId >= FLOOR(RAND() * (SELECT MAX(albumImages.albumImageId)))
)
WHERE album.organisationId = 1
AND album.languageId = 1
GROUP BY album.albumId

 

I think it's about the LIMIT, but I can't figure where to put it.  :-\

[fenway]

You've now posted two queries... did the first one produce the correct count?

[thuijzer]

You've now posted two queries... did the first one produce the correct count?

 

Yes, the first one produced the right count and even an 'url' from 'albumImages'. But I had absolutely no control over this url. I count not order it in any way.

[fenway]

What do you mean by that?

[thuijzer]

Well it just gave back an 'albumImages.url' but I don't know how te get a random one or one in some order.

 

So my problem is: how do I count all images in 'albumImages' and select a random one at the same time? I think this should be done with a LEFT JOIN, because if there are no images, my album won't be selected.

 

Thanks!

 

 

(excuse me for my poor english)

[fenway]

Did you try adding a LIMIT 1 to the end of your 2nd query?

[thuijzer]

Yes. It will then return only one record, but 'albumImages2.url' will always be the same and the COUNT is somehow multiplied with the total records I get when I don't use a LIMIT.

 

I'm starting to think it would be much quicker to just create two seperate queries...

[thuijzer]

I now dropped the COUNT and the query works fine:

SELECT
album.albumId AS albumId,
album.title AS title,
albumImages.url AS url
FROM album
LEFT JOIN albumImages
(
albumImages.albumId = album.albumId
AND albumImages.albumImageId >= FLOOR(RAND() * (SELECT MAX(albumImages.albumImageId)))
)
WHERE album.organisationId = 1
AND album.languageId = 1
LIMIT 1

Afterwards I will select a COUNT in a separate query. I don't think this well give me performance issues.

[thuijzer]

Ah, I still got a problem  :-\

 

I think the LIMIT is is the wrong place, because now I only get 1 album.

Is there a way to LIMIT the LEFT JOIN?

 

Thanks!

 

[fenway]

What's the difference? If you use an inner join, you'll necessarily get one image.

[thuijzer]

What's the difference? If you use an inner join, you'll necessarily get one image.

True, but I also get one album. And I want to select all albums:

WHERE album.organisationId = 1
AND album.languageId = 1

So my query still doesn't work  :-\

[fenway]

The way to limit the left join is the pull it out into another table with limit 1.

[thuijzer]

The way to limit the left join is the pull it out into another table with limit 1.

Do you mean a temp. table?

[fenway]

No, I mean a derived table.