UNION is ruining my ORDER BY!
Hello together!
Please read my posting, even of seems to be long at first sight..... I really would be thankful for your help!
MySQL 4.1.11
------------
(
SELECT t1.firmenname, t1.strasse, t2.platzierungsfolge AS ranking
FROM firmendaten AS t1
JOIN platzierungen AS t2 ON t2.id_firmendaten = t1.idfirmendaten
WHERE t2.platzierungswort = 'maschine'
ORDER BY ranking ASC
)
(
SELECT t1.firmenname, t1.strasse, t2.platzierungsfolge AS ranking
FROM firmendaten AS t1
JOIN platzierungen AS t2 ON t2.id_firmendaten = t1.idfirmendaten
WHERE t2.platzierungswort = 'maschine'
ORDER BY ranking ASC
)
Result:
-------
firmenname strasse ranking
Stadlberger Maschinenbau GmbH Lengloher Str. 1
Roessner Maschinenbau GmbH Alte Liederbacher Str. 2
Kaiser Ventilatorenbau KG Herrntrop 3
Expectra Deutschland GmbH Heidenkampsweg 4
Babylon Stahlbau GmbH Zeppelinstr. 5
=> O.k. fine, I get exactly the results I want.
=> Now the same queries but this time with the use of UNION:
1. Search in the table 'platzierungen' and sort the results ascending by the field 'platzierungsfolge. So if somebody enters 'machine', rows with 'machine' held in the table 'platzierungen' should be shown first, ordered by 'platzierungen'. A kind of ranking for customers who payed money to be shown on position 1,2,3,... for the search word 'machine'.
2. After the "payed" entries are shown , the search word should be found in the table 'firmendaten' (company data). There the field 'produkte' should be searched, with LIKE %machine%. Here another sorting, this time ORDER BY 'logo_ranking'.
O.k., here is my UNION statement:
MySQL 4.1.11
------------
(
SELECT t1.firmenname, t1.strasse, t2.platzierungsfolge AS ranking
FROM firmendaten AS t1
JOIN platzierungen AS t2 ON t2.id_firmendaten = t1.idfirmendaten
WHERE t2.platzierungswort = 'maschinenbau'
ORDER BY ranking
)
UNION (
SELECT firmenname, strasse, logo_ranking
FROM firmendaten
WHERE produkte LIKE '%maschinenbau%'
ORDER BY logo_ranking
)
(
SELECT t1.firmenname, t1.strasse, t2.platzierungsfolge AS ranking
FROM firmendaten AS t1
JOIN platzierungen AS t2 ON t2.id_firmendaten = t1.idfirmendaten
WHERE t2.platzierungswort = 'maschinenbau'
ORDER BY ranking
)
UNION (
SELECT firmenname, strasse, logo_ranking
FROM firmendaten
WHERE produkte LIKE '%maschinenbau%'
ORDER BY logo_ranking
)
Result:
-------
firmenname strasse ranking
Roessner Maschinenbau GmbH Alte Liederbacher Str. 2
Kaiser Ventilatorenbau KG Herrntrop 3
Hendrik Hilbring Maschinenbau Besslinghook 12
Expectra Deutschland GmbH Heidenkampsweg 4
Krifft & Zipsner GmbH Industriestraße 7
Everhard Löcher GmbH Lenenweg 8
Thomas Metz Maschinenbau GmbH Nordstrasse 10
Babylon Stahlbau GmbH Zeppelinstr. 5
Stadlberger Maschinenbau GmbH Lengloher Str. 1
Bräuer Maschinen- und Freiberger Straße 6
ORGA GmbH Wachhausstr. 11
Christian Weiße Gabelsbergerstraße 0
Jentec GmbH Meißner Straße 1
JACOBI Systemtechnik GmbH An der Prießnitzaue 0
FG Fußbodengeräte- und Am Karrenberg 1
Portatec Maschinenbau GmbH Mittelstraße 0
IKB Ingenieur- Tzschianer Straße 0
Forschungsvereinigung Heidelberger Straße 0
=> That is absolutely WRONG! (2,3,12,4,7,8,...) How can this be? Why aren't the first results sorted by 'ranking'? Just a complete mess?! Why does the 2nd statement mess up the order of my first statement? Or what is going wrong?
=> Further question: Is my way of solving my problem totally wrong? I don't think so, but one never knows... Wink
Thanks a lot!