| Combining one table to another multiple times including nulls [message #372164] |
Wed, 17 January 2001 10:11  |
Rishi
Messages: 63
Registered: January 2001
|
Member |
|
|
Here's the query I have written so far.
select si.last_name||', '||si.first_name "Name"
cs1.first_quarter_grade "English", cs2.first_quarter_grade "Math", cs3.first_quarter_grade "Science", cs4.first_quarter_grade "Social Studies"
from student_information si, class_scores cs1, class_scores cs2, class_scores cs3, class_scores cs4
where si.student_id_number=cs1.student_id_number and si.student_id_number=cs2.student_id_number and si.student_id_number=cs3.student_id_number and si.student_id_number=cs4.student_id_number
and cs1.class_code=1 and cs2.class_code=2 and cs3.class_code=3 and cs4.class_code=4 and
cs1.academic_year=2000 and cs2.academic_year=2000 and cs3.academic_year=2000 and cs4.academic_year=2000
-
What I want this query to do is display four subject grades in the same row, despite them being inserted into the table as multiple records. The query works to the extent that if all four subject grades are entered (4 records in the table), then it displays. If that isn't the case, it doesn't. I want it to display even if just one of the four subjects is entered into the table (and the other 3 subject columns would be blank). Using outer joins didn't seem to help.
Hopefully this is enough information for anyone to assist me. Thanks.
|
|
|
|
| Re: Combining one table to another multiple times including nulls [message #372173 is a reply to message #372164] |
Wed, 17 January 2001 11:56  |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
Outer joins should work provided you outer join all columns in the where clause associated with the table you want outer joined:
select si.last_name||', '||si.first_name "Name"
cs1.first_quarter_grade "English", cs2.first_quarter_grade "Math", cs3.first_quarter_grade "Science", cs4.first_quarter_grade "Social Studies"
from student_information si, class_scores cs1, class_scores cs2, class_scores cs3, class_scores cs4
where
si.student_id_number=cs1.student_id_number(+)
and si.student_id_number=cs2.student_id_number(+)
and si.student_id_number=cs3.student_id_number(+)
and si.student_id_number=cs4.student_id_number(+)
and cs1.class_code(+)=1
and cs2.class_code(+)=2
and cs3.class_code(+)=3
and cs4.class_code(+)=4
and cs1.academic_year(+)=2000
and cs2.academic_year(+)=2000
and cs3.academic_year(+)=2000
and cs4.academic_year(+)=2000
|
|
|
|
| Re: Combining one table - only 1 outer join ! [message #372174 is a reply to message #372164] |
Wed, 17 January 2001 12:25 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
you can only have one outer join per query. if you want more you need to break it up like:
select t1.x, v.y, v.z
from t1, (select t2.y, t3.z
from t2, t3
where t2.joincol=t3.joincol (+)) v
and t1.joicol=v.joincol (+);
This places each outer join in it's own query. This example uses inline views.
|
|
|
|
|
|
| Re: Combining one table - only 1 outer join ! [message #372188 is a reply to message #372174] |
Thu, 18 January 2001 07:50 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
Andrew, Rishi may outer join all three tables in the query. What you can not do is outer join one table to more than one other table.
ie:
-- this is allowed table t2, t3, and t4 are only outer joined to one table (t1)
select ..
from t1, t2, t3,t4,
where t1.c = t2.c (+)
and t1.c = t3.c (+)
and t1.c = t4.c (+)
-- this is NOT allowed table t2 is outer joined to t1, therefore it may NOT also be outer joined to table t3
select ..
from t1, t2, t3
where t1.c = t2.c (+)
and t3.c2 = t2.c2 (+)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
