Help with SQL problem

Help with SQL problem

Post by Larry Coo » Sat, 17 Jul 1999 04:00:00



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


 
 
 

Help with SQL problem

Post by Anthony Mandi » Mon, 19 Jul 1999 04:00:00



> 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.

        Yes, its a silly problem. It shouldn't be too hard for
        Sybase to fix, but it seems they aren't to interested
        in doing so. Anyway, the usual fix is to use another
        entry in the from clause for the same table (with a
        different alias or short name) or to change the
        regular join to an outer join, depending on the
        circumstances. I think the latter qualifies for you.

-am

 
 
 

Help with SQL problem

Post by tj0.. » Thu, 22 Jul 1999 04:00:00




> 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



Easy question, no cookie. The way around to this is breaking up your
query. First result set should not branch out from your inner table and
store the result set into a temporary table. Then use that temp table to
join to the last table. Good luck.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

1. Help With SQL Problem.

Hi all,

I have a connection table holding customers and products.

Table: CustomerProduct(ID, CustomerID, ProductID)

How can I get all customers that purchase all products in a given list. For
example ProductID are:(100,105,322,111)

I need to have only the customers that purchase all of the products in the
list, not part of it.

The SQL:

Select CustomerID from CustomerProduct

Where ProductID in (100,105,322,111)

Is not good, because it also returns customers with some of the products.

Thanks in advance,

Amos

2. VB5: No QueryDefs = Huge Headache!

3. HELP with SQL problem

4. Table doesn't replicate!!!

5. Help, simple SQL problem.

6. Career Required in Scotland

7. REPOST: help: hot sql problem

8. SQLServer & Jrun Drivers Problem

9. Help: PL/SQL problem

10. help: hot sql problem

11. Need help on SQL problem..

12. Please help..simle sql problem!