www.webdeveloper.com
Results 1 to 13 of 13

Thread: [RESOLVED] Randomizing results within a LEFT JOIN

  1. #1
    Join Date
    Oct 2008
    Posts
    15

    resolved [RESOLVED] Randomizing results within a LEFT JOIN

    Does anyone know how can i change this statement so it doesnt just pick the most recent entry from tbl_folderimage but instead randomises the results and then groups by folder_id.


    Code:
    SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    GROUP BY tbl_folderimage.folder_id 
    ORDER BY RAND();
    Does that make sense?

    Thanks

    James

  2. #2
    Join Date
    Sep 2006
    Location
    Bucharest, RO
    Posts
    940
    I don't know if this will work (I don't know if MySQL can select from a subquery -- I know Oracle does), but it's worth a shot:
    Code:
    SELECT * FROM (SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    ORDER BY RAND())
    GROUP BY folder_id;

  3. #3
    Join Date
    Oct 2008
    Posts
    15
    Thanks Znupi,

    You understand what I want to do but sadly this query doesnt work. I tried few queries a long these lines before. Does a left join have to be grouped to be a valid query? Ive tried grouping it first by folderimage_id and then again outside of the subquery by folder_id but that doesnt work either.

    Im at a real loss on this one...

  4. #4
    Join Date
    Sep 2006
    Location
    Bucharest, RO
    Posts
    940
    Tell us what this query does:
    Code:
    SELECT * FROM (SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    ORDER BY RAND());
    If it doesn't give an error, try:
    Code:
    SELECT * FROM (SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    ORDER BY RAND()) AS subqry
    GROUP BY subqry.folder_id;
    If any of these queries spits out errors, let us know what the errors are. I would test the queries myself but I can't really replicate your scenario (I don't have the table structure and data). Maybe we could help you a bit more if you posted an export of these tables (if they don't contain private data).

  5. #5
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    With out using the ORDER BY RAND() clause in your statement what do you get?

    Code:
    SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    GROUP BY tbl_folderimage.folder_id
    I think you are wanting to randomize the folder/images selected in the query, but if you are filtering them down the ORDER BY RAND() will only select the filter results which might be limited to a few records.

    What I think your trying to do (correct me if I'm wrong) is select a random folder of images.
    Since this is in the PHP forum and not the SQL forum I will throw out a PHP solution to think on (Not saying it's the best solution here).

    You could find the min and max record ID's in your database. to find the min you just need the first ID' number is your record set in the DB table. For example the first record ID' is going to be 100000

    Then you could use a SELECT statement to find the MAX value SELECT LAST_INSERT_ID(); (I think this will only work for the primary key).

    But now we have a MIN and MAX value.

    Now buy using the PHP RAND() with the MIN and MAX values you can find a random record id.

  6. #6
    Join Date
    Sep 2006
    Location
    Bucharest, RO
    Posts
    940
    Phill Pafford: that wouldn't be a good idea. If there are rows that have been deleted, the database could contain ids like this: 1 2 3 5 8 (for example) and if php chooses 4 or 6 or 8 then no rows will be returned.

    Also, there's mt_rand()

  7. #7
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    That would be correct, I was using an auto increment method for a primary key that increments by one. (Thanks for pointing this out) =P

  8. #8
    Join Date
    Oct 2008
    Posts
    15
    Thanks for all your help on this

    Code:
    SELECT * FROM (SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    ORDER BY RAND());
    This gives the error #1248 - Every derived table must have its own alias

    Code:
    SELECT * FROM (SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    ORDER BY RAND()) AS subqry
    GROUP BY subqry.folder_id;
    And this gives #1060 - Duplicate column name 'image_id'

    Code:
    SELECT *
    FROM (
    SELECT *
    FROM tbl_folderimage
    WHERE folderimage_rate = '5'
    ORDER BY RAND( )
    ) AS subqry
    GROUP BY folder_id
    This however does work and almost gives the information I require... So a randomised subquery is possible but it seems either the left join is upsetting things or the problem is to do with the group by

  9. #9
    Join Date
    Oct 2008
    Posts
    15
    I thought maybe the subquery had to be grouped first and then regrouped but this also doesnt work...

    Code:
    SELECT * FROM (SELECT * FROM tbl_folderimage 
    LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id 
    LEFT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id 
    WHERE tbl_folder.folder_rate='5' 
    AND tbl_folderimage.folderimage_rate='5' 
    AND tbl_folder.folder_fix='1' 
    ORDER BY RAND() 
    GROUP BY tbl_folderimage.folderimage_id
    ) AS subqry
    GROUP BY subqry.folder_id;

  10. #10
    Join Date
    Sep 2006
    Location
    Bucharest, RO
    Posts
    940
    Try:
    Code:
    SELECT * FROM (SELECT * FROM tbl_folderimage AS tbl1
    LEFT JOIN tbl_image AS tbl2 ON tbl1.image_id = tbl2.image_id
    LEFT JOIN tbl_folder AS tbl3 ON tbl2.folder_id = tbl3.folder_id 
    WHERE tbl3.folder_rate='5' 
    AND tbl1.folderimage_rate='5' 
    AND tbl3.folder_fix='1' 
    ORDER BY RAND()) AS subqry
    GROUP BY subqry.folder_id;
    If that doesn't work, try replacing the last line with:
    Code:
    GROUP BY subqry.tbl2.folder_id;
    although I really don't think it will work, it's worth a shot.

    Again, it would be really helpful if you could supply some example data and a structure for these tables (a MySQL export -- can be done easily from PhpMyAdmin) so we can fiddle around with it.

  11. #11
    Join Date
    Oct 2008
    Posts
    15
    Both times when I do this it says

    #1054 - Unknown column 'tbl2.folder_id' in 'on clause'

    Here a sql dump of the database.

    Thanks so much again for all your help with this. I dont think ive ever been this stuck before!!
    Attached Files Attached Files

  12. #12
    Join Date
    Sep 2006
    Location
    Bucharest, RO
    Posts
    940
    Ok, I've found a solution. Pretty complicated, but it works:
    Code:
    SELECT * FROM
    (SELECT t1.folder_id, t2.image_filename, t2.image_caption
    FROM tbl_folderimage AS t1
    LEFT JOIN (tbl_image AS t2, tbl_folder AS t3)
    ON (t1.image_id = t2.image_id AND t2.folder_id = t3.folder_id)
    WHERE t3.folder_rate='5'
    AND t1.folderimage_rate='5'
    AND t3.folder_fix='1'
    ORDER BY RAND()) AS subqry
    GROUP BY subqry.folder_id;
    The idea is that you have to specify exactly what columns you need, so that there are no two columns with the same name which would make the subquery give an error (an object can not have two columns with the same name).
    By the way, in the export you posted, there was no folder_id in tbl_image. Why is that? I just made one and filled it with 12.

  13. #13
    Join Date
    Oct 2008
    Posts
    15
    Thats because tbl_image and tbl_folder are only related by entries by in tbl_folderimage. But I changed t2.folder_id = t3.folder_id to t1.folder_id = t3.folder_id and it works!!!

    Thanks again Znupi, you have been so much help

    Code:
     SELECT *
    FROM (
    
    SELECT t1.folder_id, t2.image_filename, t2.image_caption
    FROM tbl_folderimage AS t1
    LEFT JOIN (
    tbl_image AS t2, tbl_folder AS t3
    ) ON ( t1.image_id = t2.image_id
    AND t1.folder_id = t3.folder_id )
    WHERE t3.folder_rate = '5'
    AND t1.folderimage_rate = '5'
    AND t3.folder_fix = '1'
    ORDER BY RAND( )
    ) AS subqry
    GROUP BY subqry.folder_id

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles