by I apologize in advance for being long-winded with this
question.
I'm getting server message 301, "The table 'course_name_history'
is an inner member of an outer-join clause. This is not allowed
if the table also participates in a regular join clause."
I understand the meaning of the error, even though I don't know
why Sybase has a problem with it. I need to find an alternate
way of doing what I want to do, and I need input.
Here's a contrived example which illustrates the situation
(I'm trying to keep this as short as possible). This is a
set of tables that tracks courses, disciplines (i.e.: prefixes)
and historical changes to courses. Here's DDL:
/* The prefix table tracks each discipline. */
create table prefix (
id numeric(10) identity,
prefix char(4),
primary key (id)
)
/* The course table tracks each course. */
create table course (
id numeric(10) identity,
prefix_id numeric(10) references prefix(id),
number int,
title varchar(80),
primary key (id)
)
/* The course_name_history tracks previous prefixes */
/* and/or numbers held by a course. */
create table course_name_history (
id numeric(10) identity,
course_id numeric(10) references course (id),
prefix_id numeric(10) references prefix (id),
number int,
date_from datetime,
date_to datetime,
primary key (id)
)
And here's sample data:
select * from prefix
id prefix
--- ------
1 MATH
select * from course
id prefix_id number title
-- --------- ------- --------------
1 1 101 Beginning Math
2 1 102 Advanced Math
select * from course_name_history
id course_id prefix_id number date_from date_to
-- --------- --------- ------ ----------- -----------
1 1 1 1 Jan 1 1991 Dec 31 1995
2 1 1 21 Jan 1 1996 Dec 31 1998
Okay, so there are two courses, MATH 101 and MATH 102.
Math 102 kept the same course number its entire life,
but MATH 101 was previously called MATH 1 and MATH 21.
I'm writing SQL to return each course that exists in the
course table. If there was a historical name for the
course, then the historical name also appears, otherwise
the historical part will be null. If there is more than
one historical name (like in this example) then there
will be more than one row returned for the course.
Here's SQL that works correctly. It returns everything
I want, except with prefix ID's instead of the prefixes
themselves:
select c.id, c.prefix_id, c.number, h.prefix_id, h.number
from course c, course_name_history h
where h.course_id =* c.id
Here is the result set:
id prefix_id number prefix_id number
-- --------- ------ --------- ------
1 1 101 3 1
1 1 101 3 21
2 1 102 NULL NULL
Okay, same thing, except look up the prefixes from the
prefix table, given the prefix_id:
select c.id, p1.prefix, c.number, p2.prefix, h.number
from course c, course_name_history h, prefix p1, prefix p2
where h.course_id =* c.id
and c.prefix_id = p1.id
and h.prefix_id = p2.id
And now I get the error message. I want the result set to
look like this:
id prefix number prefix number
-- ------ ------ ------ ------
1 MATH 101 MATH 1
1 MATH 101 MATH 21
2 MATH 102 NULL NULL
If anybody can suggest a correct way to produce this
result set, I'd appreciate it. I'm using ASE 11.9.2
on IBM RS/6000 AIX. Again, what I've posted here is
just a trivial example distilled from the real problem,
but it illustrates the problem without making this post
a hundred times longer than it already is.
Thanks.
Larry Coon
University of California