Search code examples
sqlintersystems-cache

SQL Query to exclude any items that meet a certain requirement (accounting for null)


I see similar questions but not one that answers exactly this case, so I will go ahead and ask.

I need to select a group of facilities, but once they've matched a certain set of criteria, they may not be included. So if the submission month matches a particular month and either FacWideAU, FacWideAR, or AU = 1 (not 0 or null), then that facility cannot be included. The facilities may have multiple results.

Sample DB Example (only Hosp1 should qualify):

 HospID  |  HospName        |        FacWideAU   |  FacWideAR  |  AU
 ---------------------------------------------------------------
 1          Hosp1                    0
 2          Hosp2                    1             0            0
 2          Hosp2                    0                          0
 3          Hosp3                    1             1            0
 3          Hosp3                   
 3          Hosp3                    0

etc.

Is there an easier way to write the query than the following (repeating all the selection logic, which has joins and is more convoluted in reality)?

Many thanks!

   Select distinct HospID, HospName
      from <Hospital Table>
      where Description='...whatever...'
      etc.

   and ID NOT in
   (
      Select distinct HospID 
         from <Hospital Table>
         where Description='...whatever...'
         etc.

         and (case when (TO_CHAR(SubmissionMonth, 'MON-YYYY') = 'Mar-2017' AND
         (FacWideAU = 1 or FacWideAR = 1 or AU = 1)) then 1 else 0 end) = 1
   )

Solution

  • I found another solution: You could use a left join on your table.

    Select distinct h1.HospID, h1.HospName
          from <Hospital Table> h1
          left join <Hospital Table> h2 on h1.HospId = h2.HospId
                and (((TO_CHAR(h2.SubmissionMonth, 'MON-YYYY') = 'Mar-2017')
                and (isnull(h2.FacWideAU, 0) = 1 or isnull(h2.FacWideAR, 0) = 1 or isnull(h2.AU, 0) = 1))
    
          where Description='...whatever...'
          and h2.id is null
          etc.