>Ways around inner select statments....
>Select ID, Sum(CASE WHEN phone.PHN = NULL THEN 1 ELSE 0 END) as PHNCount
>from person left outer join phone on ID
>where PHNCount = 0 GROUP BY phone.ID;
The alias in the WHERE clause is illegal; it would have to be
SELECT persons.ID,
Sum( CASE WHEN phone.type = 'PHN' THEN 1 ELSE 0 END ) AS PHNCount
FROM persons
LEFT OUTER JOIN phone USING ( ID )
GROUP BY phone.ID
HAVING phncount = 0;
which on this machine is up to ten times slower than
SELECT *
FROM persons pe LEFT JOIN phone ph ON pe.ID = ph.ID AND ph.type = 'PHN'
WHERE ph.type IS NULL;
PB
[mysql]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
