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.