List:General Discussion« Previous MessageNext Message »
From:Aamir Baig Date:May 27 2003 2:48am
Subject:Re: How to write this query pls?
View as plain text  
You would figure that an sql query like following would work (I 
modified  it slightly as it needs to take into account the type column):

select P.personid ,
SUM(CASE WHEN PH.type = 'PHN' THEN 1 ELSE 0 END) AS PHNC
 From Person P left outer join Phone PH
ON P.personid
Where PHNC = 0 group by Phone.personid;

But it says:
ERROR 1054: Unknown column 'PHNC' in 'where clause'

I know it's something trivial, can't seem to place the finger at it 
though..

Regards,
Aamir

On Monday, May 26, 2003, at 09:01  PM, TheMechE wrote:

>
> 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;
>
> Explanation:  This will select the ID's of all the Rows in person, 
> that have
> equivilent nulls for their phone phn fields.
>               It uses the Left Outer Join in order to preserve rows in 
> the
> person table that have zero rows in the phone tbl.
>               It uses a Case statment that increments the sum, if it 
> finds a
> row in the phone phn field that is not null.
>               If it is null, it icrements, if not it holds at zero.  
> You are
> only interested in the items that have
>               a sum of zero.  So we include the where exclusion.
>
>               NOTE: If you require another piece of data besides the 
> ID, you
> must include that in the select list,
>               AND also say Group By phone.ID, OtherField.ID  To be 
> able to
> obtain that data also, but it must have
>               a one to one relationship to the ID...
>
>
>
>
>
>> I have 2 tables
>>
>> persons & phone
>>
>> persons has a 1->M with phone on personID.
>>
>> phone can contain 3 'type' records relating to a person; 'EML', 'PHN',
>> 'FAX'
>>
>> I want to select _only_ those records in person which do NOT have a
>> 'type'
>> 'PHN' record in phone. Do I need a "NOT HAVING" or a "NOT IN" or...
>>
>> Basically I don't know how to write the WHERE section of the call.
>>
>> Can you help pls?
>>
>>
>> [filter: SQL, MYSQL]
>>
>>
>
> SUM( CASE WHEN TableB.dogid = 'INSERTDESIRED_Dog_ID'  THEN 1 ELSE 0 
> END) as
> OwnedIs1OrBetter
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

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