implementing multiple outer joins within the one query

[Jasper Vader]

Hi again...
i have an error come up when i try the following code...
select L.BLDG_CODE, L.ROOM, T.TERM_DESC, C.CALL_ID, CS.SEC_NUM, CS.DAY, CS.TIME
from LOCATION L, TERM T, COURSE C, COURSE_SECTION CS
where L.LOC_ID = CS.LOC_ID
and T.TERM_ID = CS.TERM_ID (+)
and C.COURSE_ID = CS.COURSE_ID (+);
but the fact is there needs to be an outer join on both.
so how do i implement more than one outer join within the one query?
any ideas - thankyou in advance.
-jasper

[Michael Matola]

i have an error come up
What error exactly?
"there needs to be an outer join on both"
What does "both" mean here? You have 4 tables and need at least 3 joins. How to understand "both" in light of this?
I think this is more of a "what are you trying to do" thing than "what is the correct syntax."
The way you have your current joins set up implies:
- Inner. There are always matching rows on location and course_section. (If there aren't, we don't care about unmatched rows on either table.)
- Outer. Term has some rows that don't have matched rows on course_section. We still want to see those rows from term.
- Outer. Course has some rows that don't have matched rows on course_section. We still want to see those rows from course.
Is this what you're intending? If not, please spell out how my assumptions are incorrect. (Or alternately, tell me what the correct assumptions are.) The core question is which tables will always have rows (for the given keys/join) and which may or may not have rows.

[Jasper Vader]

the error message that comes up is
ORA-01417: a table may be outer joined to at most one other table

so yes, you got my intention right. i will try to be clearer about my intention in future when posting, as it removes the guessing game.
now, concerning my intention, i will closely examine my assumptions to ensure that my intentions are apropriate.
thanks
i will post again once i know that i have the right assumptions.

[Jasper Vader]

Michael, I have looked at the database .. and there seems to be no need at all for outer joins, i was totally off track. The query seems to work fine as i wrote it without the outer joins. Talk about making things difficult for myself!
select L.BLDG_CODE, L.ROOM, T.TERM_DESC, C.CALL_ID, CS.SEC_NUM, CS.DAY, CS.TIME
from LOCATION L, TERM T, COURSE C, COURSE_SECTION CS
where L.LOC_ID = CS.LOC_ID
and T.TERM_ID = CS.TERM_ID
and C.COURSE_ID = CS.COURSE_ID;

I will make sure i am certain on my ASSUMPTIONS before posting anything in future, aswell as making my intention clearer.
thanks again.

[Michael Matola]

JV:
The query seems to work fine as i wrote it without the outer joins.

Works fine, but does it return the correct rows?

I will make sure i am certain on my ASSUMPTIONS before posting anything in future, aswell as making my intention clearer.
No problem. Hope I didn't seem too grumpy. It can be difficult to address some general SQL questions without more info on the tables and data.

[Jasper Vader]

thanks for your clarity Michael,
i will post again if i know that the right rows are being returned.
[ May 31, 2003: Message edited by: Jasper Vader ]