thuijzer Posted February 4, 2008 Share Posted February 4, 2008 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 Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/ Share on other sites More sharing options...
Illusion Posted February 4, 2008 Share Posted February 4, 2008 http://www.phpfreaks.com/forums/index.php/topic,125759.0.html Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-457504 Share on other sites More sharing options...
thuijzer Posted February 4, 2008 Author Share Posted February 4, 2008 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. :-\ Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-457613 Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 You've now posted two queries... did the first one produce the correct count? Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-457633 Share on other sites More sharing options...
thuijzer Posted February 4, 2008 Author Share Posted February 4, 2008 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. Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-457668 Share on other sites More sharing options...
fenway Posted February 4, 2008 Share Posted February 4, 2008 What do you mean by that? Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-457753 Share on other sites More sharing options...
thuijzer Posted February 5, 2008 Author Share Posted February 5, 2008 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) Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-458479 Share on other sites More sharing options...
fenway Posted February 5, 2008 Share Posted February 5, 2008 Did you try adding a LIMIT 1 to the end of your 2nd query? Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-458637 Share on other sites More sharing options...
thuijzer Posted February 5, 2008 Author Share Posted February 5, 2008 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... Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-458674 Share on other sites More sharing options...
thuijzer Posted February 5, 2008 Author Share Posted February 5, 2008 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. Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-458683 Share on other sites More sharing options...
thuijzer Posted February 5, 2008 Author Share Posted February 5, 2008 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! Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-458732 Share on other sites More sharing options...
fenway Posted February 5, 2008 Share Posted February 5, 2008 What's the difference? If you use an inner join, you'll necessarily get one image. Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-458759 Share on other sites More sharing options...
thuijzer Posted February 6, 2008 Author Share Posted February 6, 2008 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 :-\ Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-459564 Share on other sites More sharing options...
fenway Posted February 6, 2008 Share Posted February 6, 2008 The way to limit the left join is the pull it out into another table with limit 1. Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-459865 Share on other sites More sharing options...
thuijzer Posted February 7, 2008 Author Share Posted February 7, 2008 The way to limit the left join is the pull it out into another table with limit 1. Do you mean a temp. table? Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-460713 Share on other sites More sharing options...
fenway Posted February 8, 2008 Share Posted February 8, 2008 No, I mean a derived table. Link to comment https://forums.phpfreaks.com/topic/89336-select-count-and-random-field-in-left-join/#findComment-461403 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.