List:General Discussion« Previous MessageNext Message »
From:TheMechE Date:May 26 2003 4:01pm
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;

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

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