Re: reforming query for 7.0.2

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