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
>
