MySQL Forums
Forum List  »  Newbie

Re: union and missing limits
Posted by: M B
Date: April 08, 2010 09:11AM

Thanks for your reply Rick!

Below is the query I have now, but it gives this error:
... near 'UNION ALL (
SELECT test2_boxes.own_user, test3_boxinv.color, test3_boxinv.piec' at line 7

Without inner aliases, error is: Every derived table must have its own alias

I need those limits for sub-selects, because without them query is slow and getting slower and slower as I add more data.
And yes, limit is used for pagination - never needed to think another way to do it (until now?)


( SELECT  user, color, piece, SUM(quantity) AS quantity FROM
     
     ( SELECT test1_loose.user,test1_loose.color,
              test1_loose.piece,test1_loose.quantity
        FROM  test1_loose
        ORDER BY user
        LIMIT 0,50 ) as s1

      UNION ALL 

      ( SELECT test2_boxes.own_user,test3_boxinv.color,
               test3_boxinv.piece,
              (test3_boxinv.quantity * test2_boxes.own_quantity) AS quantity
        FROM  test2_boxes,test3_boxinv
        WHERE  test2_boxes.own_item = test3_boxinv.item
        ORDER BY user
        LIMIT 0,50 ) as s2

      ) as uu
    GROUP BY user, color, piece ORDER BY user LIMIT 0,50



Edited 1 time(s). Last edit at 04/08/2010 09:35AM by M B.

Options: ReplyQuote


Subject
Written By
Posted
M B
March 27, 2010 04:49AM
March 27, 2010 10:35AM
M B
March 27, 2010 11:45AM
March 28, 2010 12:33PM
M B
March 28, 2010 02:53PM
M B
April 08, 2010 01:02AM
M B
April 08, 2010 01:41AM
April 08, 2010 07:51AM
Re: union and missing limits
M B
April 08, 2010 09:11AM
April 09, 2010 12:33AM
M B
April 09, 2010 03:59AM
April 09, 2010 09:42AM
M B
April 09, 2010 01:06PM
April 09, 2010 09:41PM
M B
April 12, 2010 03:48AM
April 08, 2010 07:56AM
April 08, 2010 08:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.