select rows with NULL & non-null values

inquisite

DW Fresher

Reputation: +3/-0
Offline

Offline

Posts: 7

select rows with NULL & non-null values

« on: January 18, 2010, 09:26:31 pm »

I would like to select rows with (NULL & non-NULL) combination

student_course table

id,student,subject
1, 100, NULL
2,100, Science
3,200, Mathematics
4,200, science
5, 300, English
6, 400, NULL
7, 500, Mathematics
8, 500, NULL
9, 600, Science

Desired output:-

id,student,subject
1, 100, NULL
2, 100, Science
7, 500, Mathematics
8, 500,

I do not want to select the row with student id 400 as it only contains NULL.
I would like to see a combination of NULL & not null values.

Any help is appreciated.


	Logged

inquisite

DW Fresher

Reputation: +3/-0
Offline

Offline

Posts: 7

Re: select rows with NULL & non-null values

« Reply #1 on: January 18, 2010, 10:21:43 pm »

Using the below query, I am able to retrieve the expected data but I was wondering if there is a better way to do this.

create table student_course(id number,student number,subject varchar2(30));

insert into student_course values(1,100,NULL);
insert into student_course values(2,100,'Science');
insert into student_course values(3,200,'Mathematics');
insert into student_course values(4,200,'Science');
insert into student_course values(5,300,'English');
insert into student_course values(6,400,NULL);
insert into student_course values(7,500,'Mathematics');
insert into student_course values(8,500,NULL);
insert into student_course values(9,600,'Science');

commit;

select * from student_course where student in (select student
from student_course
group by student
having
min(nvl(subject,'0'))='0' and count(distinct subject)>=1);


	Logged

Whoever

Administrator

Reputation: +34/-0
Offline

Offline

Posts: 124

Re: select rows with NULL & non-null values

« Reply #2 on: January 19, 2010, 11:10:38 am »

Quote from: inquisite on January 18, 2010, 10:21:43 pm

Using the below query, I am able to retrieve the expected data but I was wondering if there is a better way to do this.
...

select * from student_course where student in (select student
from student_course
group by student
having
min(nvl(subject,'0'))='0' and count(distinct subject)>=1);

The highlighted part could cause a problem.
Firstly it involves a min() operator applied on a string column, depending on it to return '0' as the min() value.
What about empty string '' in subject ?

Your requirement is specifically to get students that have both nulls and non-nulls in "subject" -this query will also get you students that have the string '0' in subject.
Whether or not '0' could ever appear in subject would be debatable.


	Logged

If most people said what’s on their minds, they’d be speechless.

Pages: [1] Go Up

« previous next »