Re: Very Rusty with SQL and Oracle (mainly SQL)

From: Relvinian <m_at_msn.com>
Date: Thu, 2 Sep 2004 23:21:27 -0600
Message-ID: <taGdnQD2xuohY6rcRVn-tQ@comcast.com>

Sorry, accidentally sent it before I was ready. I have added more to the bottom of this to help fill in some of the information I didn't get a chance to.   "Relvinian" <m_at_msn.com> wrote in message news:J5ednXx907U_YKrcRVn-rw_at_comcast.com...   Hey all,

  I'm trying to get a query to work in Oracle and having troubles. I'll hopefully try and explain what I'm looking for in a result set and what my current SQL code looks like.

  What the result set should look like is the following (of course, multiple rows for each instructor at the campus, etc):

  NAME             Campus #             Group ID                  Course ID

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

  <first> <last>   <campus num>      <a number>              <course num>

  Now, the information to fill the query set is coming from four different tables.

  Here's two seperate query which give me all the information I need but I would like to get them combined into one query.

  First Query:
  select

    to_char(NULL) "Name",
    cg.orga_# "Campus #",
    cg.grp_id "Group ID",

    co.cour_id "Course ID"

  from

     cour_off co,
      inst_rost ir,
     calendar_group cg

  where


     ( co.grp_id = cg.grp_id and co.orga_# = cg.orga_# ) and

     ( co.orga_# = ir.orga_# (+) ) and

     ( co.cour_off_# = ir.cour_off_# (+) ) and

     ( cg.orga_# = 3 ) and

     ( cg.calendar_end_date > trunc(sysdate) ) and

     ( cg.grp_id like 'ADVIOG161%' )

  This gives me everything I need but the instructor's name.

  The second query:
  select
    to_char(a.alias_fnam || ' ' || a.alias_lnam) "Name",

    cg.orga_# "Campus #",
    cg.grp_id "Group ID",
    co.cour_id "Course ID"

  from
     cour_off co,
     alias a, 
      inst_rost ir,
     calendar_group cg

  where


     ( co.grp_id = cg.grp_id and co.orga_# = cg.orga_# ) and

     ( co.orga_# = ir.orga_# (+) ) and

     ( co.cour_off_# = ir.cour_off_# (+) ) and

     ( a.pers_# = ir.pers_# ) and 

     ( cg.orga_# = 3 ) and

     ( cg.calendar_end_date > trunc(sysdate) ) and

     ( cg.grp_id like 'ADVIOG161%' )

  This is almost what I need but missing information. If the ir.pers_# is null then I don't get the entry in my result set.

  Any help would be appreciated in "fixing" my query to work correctly.

  If I haven't explained well enough, let me know please.

  Relvinian Received on Fri Sep 03 2004 - 00:21:27 CDT