mysql having problems with UNION query

Question

I cannot seem to get a result for the following query, the following query return NULL

query union

"SELECT isbn    

    FROM ".TBL_BOOKS." 

    WHERE rank < 200000 AND rank IS NOT NULL 
    AND lowest_new_price < amazon_new_price/100*80 
    AND amazon_new_price < 9999 
    AND ROUND((amazon_new_price/100*80) - lowest_new_price) > (lowest_new_price/100*".MARGIN.") 

UNION 

SELECT isbn 

    FROM ".TBL_BOOKS." 

    WHERE rank < 200000 AND rank IS NOT NULL 
    AND lowest_used_price < amazon_used_price/100*80 
    AND amazon_used_price < 9999 
    AND amazon_used_price < amazon_new_price 
    AND ROUND((amazon_used_price/100*80) - lowest_used_price) > (lowest_used_price/100*".MARGIN.") 

ORDER BY rank ASC";

but the two quires work fine on their own

query 1

"SELECT isbn    

    FROM ".TBL_BOOKS." 

    WHERE rank < 200000 AND rank IS NOT NULL 
    AND lowest_new_price < amazon_new_price/100*80 
    AND amazon_new_price < 9999 
    AND ROUND((amazon_new_price/100*80) - lowest_new_price) > (lowest_new_price/100*".MARGIN.") 
ORDER BY rank ASC";

query 2

"   SELECT isbn 

        FROM ".TBL_BOOKS." 

        WHERE rank < 200000 AND rank IS NOT NULL 
        AND lowest_used_price < amazon_used_price/100*80 
        AND amazon_used_price < 9999 
        AND amazon_used_price < amazon_new_price 
        AND ROUND((amazon_used_price/100*80) - lowest_used_price) > (lowest_used_price/100*".MARGIN.") 

ORDER BY rank ASC";

If I broke up the union and ran both queries separately I would have no problems, but for some reason the UNION query does not work, what could be the problem? Thanks

What doesn't work? Tell us more exactly what happens. Is there a mysql error? a PHP error? — Jocelyn, Aug 14 '12 at 17:59
Note that `union` removes duplicates. To keep duplicates (and save the expensive duplicate removal) use `union all` — Andomar, Aug 14 '12 at 18:00

score 3 · Accepted Answer · answered Aug 14 '12 at 18:00

3

I think - when you use an 'order by' clause in a union - then the 'order by' parameter has to be one of the fields returned by the individual queries.

answered Aug 14 '12 at 18:00

Ken Benson

302
2
8

1

Yes when I answered the earlier question about this, there was a rank field and another field to determine to flag if this was new or used (though you may not need that one). I only moved the rank to outside of the individual selects so you could get a sort across the union. If you leave the sorts in the individual selects you will only have sorts within those portions of the result set. – Mike Brant Aug 14 '12 at 18:03
@Mike this is a similiar function, which only returns the isbn number – mk_89 Aug 14 '12 at 18:05
There's another topic dealing with this that might have some useful information, too... http://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union – Ken Benson Aug 14 '12 at 18:06

mysql having problems with UNION query

1 Answers1