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 Linux
distros (like Red Hat and SuSE) now come with 7.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, the
following 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) as
no_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 a
LEFT 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) as
no_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 information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco