the below one is my table row structure
select c1 from table where c7 is not null
i need c1 value if c7 if any one of the row is having null value it should not
come may i know who can i do it..
right now i am getting column which is having values but i need if one time is having null values that entry should not come.
c1 c2 c3 c4 c5 c6 c7
31 1 1 BOQ NULL 1 Item5
31 2 1 BOQ NULL 2 Item5
31 3 2 BOQ NULL 3 Itmem7
31 4 3 BOQ NULL 4 Item9
31 5 4 BOQ NULL 5 Item5
31 6 5 BOQ NULL 6 Item5
31 7 6 BOQ NULL 7 NULL
31 8 7 BOQ NULL 8 NULL
31 9 8 BOQ NULL 9 NULL
31 10 9 BOQ NULL 10 NULL
32 1 1 BOQ NULL 1 NULL
32 2 2 BOQ NULL 2 NULL
33 1 0 BOQ NULL 1 NULL
33 2 1 BOQ NULL 3 NULL
select c1 from table group by c1 having sum(c7 is null) = 0
This returns all values of c1 for which there is no c7 value that is null.
For a more generally useful ( all databases) solution, use:
having sum(case when c7 is null then 1 end) = 0
having count(c7) = count(*)
NOT EXISTS version:
select c1 from tablename t1 where not exists (select 1 from tablename t2 where t1.c1 = t2.c1 and t2.c7 is not null)
Perhaps I misunderstood OP. Above answer returns c1 values who have only NULL for c7.
If the question is to find c1 values with no NULL for c7, change the sub-select's
IS NOT NULL to
If a c1 value should be returned only once even if several occurrences, do