OUTER JOINS difference - maybe a bug?
Author Message
OUTER JOINS difference - maybe a bug?

Last time nobody answered to my question?
Is it a known bug?

Using SQL-Language of the BDE the following problems occured:

Guess, we have 3 tables:
1) "Teachers" with a fields ID and a name
2) "Courses" with a field ID, a name and a Teacher-ID
3) "Students" with a field ID, a name and a Course-ID

For example these tables content the following data:
Teachers
========
ID  Name
99  Smith

Courses
=======
ID   Name     Teacher
1    Course 1

Students
========
ID   Name          Course
900  Bart Simpson  1

Notice, that no Teacher-ID is given in the Courses-Table.

In this situation the following SQL-Queries give a different result:
SELECT *
FROM
(Courses C INNER JOIN Students S ON S.Course=C.ID)
LEFT OUTER JOIN Teachers T ON C.Teacher=T.ID

SELECT *
FROM
(Courses C LEFT OUTER JOIN Teachers T ON C.Teacher=T.ID)
INNER JOIN Students S ON S.Course=C.ID

In the second case a sentence is given back, in the first case not.
Should'nt this be the same? And if not, why not?

Hoping, someone find the difference and tells me what to do.

Thanks

Dominik Luebbers

Wed, 18 Jun 1902 08:00:00 GMT
OUTER JOINS difference - maybe a bug?
I think this is a problem of the SQL Statement:

Quote:

> In this situation the following SQL-Queries give a different result:
> SELECT *
> FROM
> (Courses C INNER JOIN Students S ON S.Course=C.ID)
> LEFT OUTER JOIN Teachers T ON C.Teacher=T.ID

they do not do the same

Quote:

> SELECT *
> FROM
> (Courses C LEFT OUTER JOIN Teachers T ON C.Teacher=T.ID)
> INNER JOIN Students S ON S.Course=C.ID

> In the second case a sentence is given back, in the first case not.
> Should'nt this be the same? And if not, why not?

> Hoping, someone find the difference and tells me what to do.

> Thanks

> Dominik Luebbers

First statement first find a records (if parsed from left to right),
then tries to make an outer join and fails. Hmmmm.

Couldn't say why, but other SQL Server's show the same behavior.

They try to build a 'Cartesian Product' of the datasets if they should
make an outer join ....
I remember in mathematics that this product isn't the same if you
exchange parameters .... long ago, far away...

Andre

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 2 post ]

Relevant Pages