List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 27 2003 4:35pm
Subject:Re: How to write this query pls?
View as plain text  
>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]


Thread
How to write this query pls?Titan 16026 May
  • Re: How to write this query pls?Becoming Digital26 May
    • RE: How to write this query pls?Titan 16026 May
      • Re: How to write this query pls?Becoming Digital26 May
        • Re: How to write this query pls?Aamir Baig26 May
          • RE: How to write this query pls?TheMechE26 May
            • Re: How to write this query pls?Aamir Baig27 May
              • Re: How to write this query pls?Dan Nelson27 May
            • Re: How to write this query pls?Peter Brawley27 May
      • Re: How to write this query pls?Peter Brawley26 May
RE: How to write this query pls?Titan 16026 May