Clever Geek Handbook

Selecting all records for which the flag is not set

How do I find everything bookingNo

where the flag is hasPaid

not set to 0?

Can't get it to work. I must receive a booking 2-4-8 if the flag is hasPaid

not set to 1

Many thanks

    if object_id('tempdb..#testFlag') is not null
        drop table #testFlag

    create table #testFlag (BookingNo int,  HasPaid bit)


    insert into #testFlag (BookingNo, HasPaid)
      select 1, 0 union ALL
      select 1, 1 union ALL
      select 2, 0 union ALL
      select 3, 0 union ALL
      select 3, 1 union ALL
      select 3, 0 union ALL
      select 4, 0 union ALL
      select 4, 0 union ALL
      select 4, 0 union ALL  
      select 5, 0 union ALL
      select 5, 1 union ALL
      select 6, 0 union ALL
      select 6, 1 union ALL
      select 7, 0 union ALL
      select 7, 1 union ALL
      select 8, 0
+3


source to share


3 answers


You can use GROUP BY

with CASE

based HAVING

like this.

Query

  SELECT BookingNo FROM #testFlag
  GROUP BY BookingNo
  HAVING SUM(CASE WHEN HasPaid = 1 THEN 1 ELSE 0 END) = 0


Output

BookingNo
2
4
8
+3


source 


select BookingNo 
  from #testFlag  
except
select BookingNo 
  from #testFlag 
 where HasPaid = 1

Besides



If you want to use a group then

SELECT BookingNo FROM #testFlag
 GROUP BY BookingNo
HAVING MAX(cast(HasPaid as int)) = 0;
+2


source


I think the problem is with duplicate BookingNo. If you want to select all BookingNo where HasPaid is not 0, try this:

  select distinct BookingNo from #testFlag t1 where HasPaid != 0
  and not exists (select 1
  from #testFlag t2 where HasPaid = 0 and t2.BookingNo = t1.BookingNo
  )

If you want to select all BookingNo where HasPaid is not 1, try this:

  select distinct BookingNo from #testFlag t1 where HasPaid != 1
  and not exists (select 1
  from #testFlag t2 where HasPaid = 1 and t2.BookingNo = t1.BookingNo
  )
0


source






More articles:


All Articles