Noob SQL question - Need to pull where condition is TRUE on one row but never FALSE on another row



  • I'm querying contract data where clients have contracts that get termed and reinstated. Each row on the table has the Client ID, Effective Date, and Term date of each contract so clients can show up multiple times if they have reinstated their contract. I only want to pull clients that show up with a term date and don't have a new row with an active contract.

    What I'm doing right now is: selecting all clients with a term date - saving into a spreadsheet selecting all clients showing active - saving into a spreadsheet

    v-lookup removing termed clients who also show up in the active spreadsheet. I know there has got to be a better way to do this.



  • I think you want something like this:

    select                        -- show 
        c.Client_ID, 
        c.Effective_Date, 
        c.Term_Date
    from                          -- all contracts
        contracts as c
    where                         -- from clients
        not exists                -- without
        ( select 1 
          from contracts as ac    -- active contracts
          where ac.Term_Date is null
            and ac.Client_ID = c.Client_ID
        ) ;
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2