NOT EXIST within same table 1

[Belle19]

I need to pull check_numbers out of a table that have a fl_flag = '1' but to exclude them if there is a row in the same table with the same check_number but the fl_flag = '0'.

How would I go about doing this in an efficient way??? I am trying to use the NOT EXIST but not sure if this is the best way. Please advice.

 

[gmmastros]

Maybe this...

Select check_number
From   table
Group By check_number
Having Count(*) = Sum(fl_flag)

This assumes that the only acceptable values in the field are 1 and 0. Basically, the number of records (per check_number) must match the sum of the flag column.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Belle,
You don't need the NOT EXISTS.

This will work if I understand what you want.

SELECT checknumber
FROM table
WHERE fl_flag = 1

If the value in the fl_flag field is an int you don't need the quotes.

 

[SQLDenis]

select check_numbers from tablename t1 where t1.flag =1
and not exists(select * from tablename t2 where t2.flag = 0 and t2.check_numbers =t1.check_numbers)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Belle19]

I think I might have not explained this completely. I need the check_number to set to a report only if there isn't ANY rows in the table where the check_number = '0'. I can't just do where check_number = '1' cause then there might be one out there where the check_number is the same but the fl_flag might be '0' on the entry. I am almost sure the only options are 1 and 0 but do not want to rely on this just in case. There might be other values the user wants to put in there.

Also the fl_flag field is a text field so the quotes are needed.

 

[Belle19]

Thank you SQLDenis!!

 

[SQLDenis]

np, tx 4 star

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/