MYSQL UNION across 3 tables with ORDER BY

Question

We have this statement:

(SELECT res_bev.bev_id, property_value.name AS priority 
    FROM res_bev, bev_property, property_value 
    WHERE res_bev.res_id='$resIn'
        AND bev_property.bev_id=res_bev.bev_id
        AND bev_property.type_id='23'
        AND property_value.id=bev_property.val_id)
UNION
(SELECT res_bev.bev_id, property_value.name as priority 
    FROM res_bev, bev_property, property_value 
    WHERE res_bev.res_id='$resIn' 
        AND bev_property.bev_id=res_bev.bev_id 
        AND bev_property.type_id='22' 
        AND property_value.id=bev_property.val_id)

We have Three Tables:

Res_bev
res_id | bev_id | id

Bev_property
type_id | val_id | bev_id | id

Property_value
name | id

What I am looking for is the results to be ordered by glass price(type_id='23') then bottle price(type_id='22') however it seems the union includes duplicates due to fact the first select returns say 3456 | 7.5 and the second returns 3456 | 55 since the price/Glass is 7.5 and the price/Bottle is 55; how can I eliminate these duplicates form the second SQL statement to return and ordered table?

Also, fooled with creating a pseudo-table via left joins to create a table of bev_id | price/Glass | price/Bottle, however since this should be able to expand to multiple price types I figured a UNION would be more efficient. Just a push in the right direction would be helpful.

I think this answer may help. http://stackoverflow.com/questions/3730093/sort-sql-records-based-on-matched-conditions — Alan Hollis, Jun 18 '12 at 21:52

Harald Brinkhof · Accepted Answer · 2012-06-18T22:09:59.697

1

You can do it in 1 query by specifying bev_property.type_id to match against an IN() clause with the values inside.

To return only the first one found you should require a DISTINCT SELECT of the accompagnying field bev_id.

To ORDER them just add an appropriate descending ORDER BY clause. This should order first and the filter out the second bev_property.type_id value. (Databases never return anything in a specific order unless you tell them to, some might have an internal convention or it might appear they do but this is never guaranteed to be repeatable unless you specify an ORDER BY clause in your SELECT statement. )

SELECT DISTINCT res_bev.bev_id, property_value.name AS priority 
FROM res_bev, bev_property, property_value 
WHERE res_bev.res_id='$resIn'
    AND bev_property.bev_id=res_bev.bev_id
    AND bev_property.type_id IN ('23','22')
    AND property_value.id=bev_property.val_id
ORDER BY bev_property.type_id DESC;

A UNION won't really be faster since you'd have to do the whole lookup twice and if you don't have this field indexed then you'll do a whole table traversal with match against 1 element twice as opposed do 1 table traversal that matches against 2 elements. (walking over a whole table is what's generally slow, not matching simple elements against each other)

When properly indexed I think you might have a tiny overhead of executing a new select query and the query analyzer running again but I don't know for sure. It'll probably be smart enough to recognise the similarities between the queries so it won't matter.

It doesn't always hurt to try on specific databases though. Whenever you try query optimisation with different statements use them with EXPLAIN, this will show you what the query will be doing and wether it'll go over whole tables, sort data on file, etc...

edited Jun 18 '12 at 22:09

answered Jun 18 '12 at 21:58

Harald Brinkhof

4,375
1
22
32

Excellent, and the explanation is much appreciated - After I posted this and thought about it, I figured there would be a better method than UNION, especially when were dealing with thousands of records. One issue, however, the results are still not ordered correctly - [Screen Shot](http://aaronmelhorn.com/sqlResults.png) - as you can see there. They should be completely DESC, but it seems that order is negated... – MaliciousMelhorn Jun 18 '12 at 22:32
you're ordering on a field that's invisible to make sure that in case of a duplicate the higher value ones (23) win out, maybe you want to order on res_bev.bev_id or property_value.name instead as well? So order by bev_property.type_id DESC, property_value.name DESC probably? – Harald Brinkhof Jun 18 '12 at 22:35
basically if you want to affect its output order: add extra order by parameters. The sole purpose of the first one is to make sure the correct duplicates get filtered out. – Harald Brinkhof Jun 18 '12 at 22:39
Oh oh oh; Gotcha, I'll play around with this tonight to get it 100% and let everyone know what I ended up with; Thanks a million for the help @Harald – MaliciousMelhorn Jun 18 '12 at 22:48
don't forget that you can use a subselect to provide the data to order: so SELECT res_bev.bev_id, property_value.name AS priority FROM (*our query here*) c ORDER BY priority DESC; this filters first and then takes the results and orders them like you want without affecting the filter. It'll be a tad slower but not by much. :) – Harald Brinkhof Jun 18 '12 at 22:50
Much Thanks, got it working by adding property_value.name+0 ASC after the DESC; It's just a little off due to the items without one or the other price being sorted (what seems like randomly) - however I think with a case I can fix that. If I need more help I'll ask another question - I like a bit of a challenge ;) – MaliciousMelhorn Jun 18 '12 at 22:58

score 1 · Answer 2 · answered Jun 18 '12 at 22:02

Unless I'm missing something, or you have from your question

SELECT res_bev.bev_id, 
       property_value.name AS priority 
FROM res_bev, bev_property, property_value 
WHERE res_bev.res_id='$resIn' 
AND bev_property.bev_id=res_bev.bev_id 
AND (bev_property.type_id='23' OR bev_property.type_id='22')
AND property_value.id=bev_property.val_id)
order by bev_property.type_id desc

PS if you want to order a union

try something along the lines of

Select * from
(
select ...
Union
select ...
) somenameforqryinparentheses
Order by Somecolumn1, somecolumn2

MYSQL UNION across 3 tables with ORDER BY

2 Answers2