Re: How to write this query pls?

[Peter Brawley]

>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]