Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Unanswered: LIMITing problem in 4.0

    Ok I have a query that entails a sub-query it works in 4.1 and higher but I have to get it to work in 4.0 environment. The query is kind of straight forward, I don't think I need to post the table structure, but please request If you think other wise.
    SELECT *
    FROM
    (SELECT * FROM `user` `s_u` LIMIT 2) `u`
    LEFT OUTER JOIN `user_genre` `ug` ON(`u`.`user_id` = `ug`.`user_id`)
    LEFT OUTER JOIN `sub_genre` `sg` ON(`sg`.`sub_genre_id` = `ug`.`sub_genre_id`)
    LEFT OUTER JOIN `genre` `g` ON(`sg`.`genre_id` = `g`.`genre_id`)
    ORDER BY `u`.`date_added` DESC, `g`.`genre_id`;

    basically I'm trying to join a bunch of tables together and LIMIT by something else. Obviously the problem is:
    FROM
    (SELECT * FROM `user` `s_u` LIMIT 2) `u`

    Is there a way to do this without using a subquery. Please any help or ideas will be greatly apreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which two users? you have no ORDER BY for the LIMIT so you are getting any random two users

    if you know which two, there might be another way to do this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2005
    Posts
    5

    no no unforutuanetly

    the LIMIT 2 is a random number, sorry for being unclear, it was just a development/test query in the real thing I would put an ORDER BY.
    so to refine the query, I will order by the date that a user was added
    SELECT *
    FROM
    (SELECT * FROM `user` `s_u`ORDER BY `s_u`.`added` LIMIT 10) `u`
    LEFT OUTER JOIN `user_genre` `ug` ON(`u`.`user_id` = `ug`.`user_id`)
    LEFT OUTER JOIN `sub_genre` `sg` ON(`sg`.`sub_genre_id` = `ug`.`sub_genre_id`)
    LEFT OUTER JOIN `genre` `g` ON(`sg`.`genre_id` = `g`.`genre_id`)
    ORDER BY `u`.`date_added` DESC, `g`.`genre_id`;

    any help or links will greatly appreciated

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well since you need to get it to work in the version previous to the version previous to the version previous to the current version, you can do it if you query the 10 users separately, and then do this --
    Code:
    SELECT u.foo
         , ug.bar
         , sg.qux
         , g.fap
      FROM `user` as u
    LEFT OUTER 
      JOIN user_genre ug 
        ON ug.user_id = u.user_id 
    LEFT OUTER 
      JOIN sub_genre sg 
        ON sg.sub_genre_id = ug.sub_genre_id
    LEFT OUTER 
      JOIN genre g 
        ON g.genre_id = sg.genre_id 
     where u.user_id
        in ( 23, 2, 45, 8, 5, 117, 80, 7, 9, 37 )
    ORDER 
        BY u.date_added DESC
         , g.genre_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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