Markus,
> how do I reform this query to work with 7.0.2?
Better question: Why are you working with 7.0.2? Even the mass-market Linuxdistros (like Red Hat and SuSE) now come
with7.1.x.
> select * from personen join (select count(personen_id), personen_id from
> orders group by personen_id) as ordertemp on personen.personen_id =
> ordertemp.personen_id
Acutally, this query needs some reforming on its own. While it will work, thefollowing version will achieve the same
result,much faster, in 7.1 (and 7.2,for that matter):
SELECT personen.field1, personen.field2, personen.field3, count(order_id) asno_orders
FROM personen LEFT OUTER JOIN orders ON personen.personen_id =orders.personen_id
GROUP BY personen.field1, personen.field2, personen.field3
... you see, your subselect above is completely unnecessary. And slower than aLEFT OUTER JOIN. Simplicity,
simplicity,simplicity!
Now, we have the problem of no LEFT JOIN support in 7.0, so:
SELECT personen.field1, personen.field2, personen.field3, count(order_id) asno_orders
FROM personen JOIN orders ON personen.personen_id = orders.personen_id
GROUP BY personen.field1, personen.field2, personen.field3
UNION
SELECT personen.field1, personen.field2, personen.field3, 0 as no_orders
FROM personen
WHERE NOT EXISTS (SELECT personen_id FROM orders WHERE personen_id =personen.personen_id);
Share & Enjoy!
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
