CODEWITHSUNDEEP
oracle-databasecount
Monteiro
Monteiro

Reputation: 217

Count with WHERE clause from two tables in Oracle?

I have two tables, one called STUDENTS and the other CLASSES. I have to select all the students that are from the same class of one student, and this student has his own number id, and through this number id that I have to select everything.

TABLE STUDENTS

nr_rgm
nm_name
nm_father
nm_mother
dt_birth
id_sex

TABLE CLASSES

cd_class
nr_schoolyear
cd_school
cd_degree
nr_series
cd_class
cd_period

So I tried something like that :

SELECT count(*) FROM students, classes WHERE id_sex = 'M' AND
cd_class = (SELECT cd_class FROM classes WHERE nr_rgm = '12150');

But then it points an error, and the error is the follow :

single-row subquery returns more than one row

So, how can I make this work ?

Upvotes: 0

Views: 172

Answers (2)

gvenzl
gvenzl

Reputation: 1891

I think what you really would want to do is to simply join the two tables together rather than issuing a sub select:

SELECT count(*)
 FROM students s, classes c
  WHERE s.id_sex = 'M' AND c.nr_rgm = '12150' AND s.cd_class = c.cd_class;

This way you just tell the database: Please count all the occurrences where my students.id_sex = 'M' and my classes.nr_rgm = '12150' AND all found studends.cd_class match those of my classes.cd_class.

The reason why your statement above fails is because the ordinary = operation, when not used in a join, will only expect one single value, like you do with s.id_sex='M' while your statement returns multiple values. To cope with that you have to use the IN operator which operates on lists.

However, you can and will achieve the very same with just joining the two tables together, and it will be much more efficient on bigger data sets.

One more note to the example above. If classes.nr_rgm is a field of data type NUMBER, don't use the ' around the value 12150 as it will lead to implicit type conversion. With other words, '12150' is a string and will have to be converted to NUMBER first before doing a comparison.

Upvotes: 1

s0771416
s0771416

Reputation: 31

you should use "in" and not "=" when applying subselects.

Upvotes: 2

Related Questions