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
