• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Liutauras Vilda
Sheriffs:
  • Paul Clapham
  • Jeanne Boyarsky
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
Bartenders:
  • Jesse Duncan
  • Frits Walraven
  • Mikalai Zaikin

implementing multiple outer joins within the one query

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
pie. tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic