How to Make the Count Right

From: Steve <schen_at_prodigy.net>
Date: 25 Apr 2003 13:42:08 -0700
Message-ID: <b166c214.0304251242.3c2abb60@posting.google.com>

I have 4 tables and a code to count the emx_addr, but the filter seems to go out of control. The table desc gets the basic columns to simplify this issue:

TABLE DESC

1. student (14,000 counts) STUDENT_ID NOT NULL NUMBER(9) LAST_NM VARCHAR2(20) FIRST_NM VARCHAR2(15) EMX_ADDR VARCHAR2(50)
2. class (43,000 counts) STUDENT_ID NOT NULL NUMBER(9) CLASS_ID NOT NULL NUMBER(9)
3. dept (6,000 counts) CLASS_ID NOT NULL NUMBER(9) CONT_ID NOT NULL VARCHAR2(10)
4. project (1,000 counts) CONT_ID NOT NULL VARCHAR2(10) ACTIVE_IND VARCHAR2(1) ACTIVATE_DT DATE

It needs to count the emx_addr from student table, which select those who have the control of active_ind of "Y" on the project table. Due to the design, project table can be related to dept table by cont_id, then dept.class_id can be related to class.class_id, then class.student_id can be related to student.student_id.

count(student.emx_addr) alone yields 12,000. But using the following coding yields about 600,000!

coding:

---

select count(a.emx_addr)
from

   student a,
   class c,
   dept d,
   project p
where

p.cont_id = d.cont_id and
c.class_id = d.class_id and
c.student_id = a.student_id and
p.active_ind = 'Y' and
a.emx_addr is not null;


----------------------------------

How to make it right, without restructing the tables?

Appreciated.

Steve Received on Fri Apr 25 2003 - 15:42:08 CDT