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  
 
 [ 2 post ] 

 Relevant Pages 

1. Outer Joins in BDE - maybe a bug

2. InterBase 4.2 bug with left outer joins

3. Help with outer join in SQL

4. SQL - More than one OUTER JOIN

5. Paradox & Outer Joins

6. DELPHI/SQL : OUTER JOIN PROBLEM ???

7. TQuery and SQL's OUTER JOINS

8. Problem doing OUTER joins w/ pdox tables

9. SQL left outer join question

10. Outer joins in Database Desktop

11. Problems with SQL-Outer Joins

12. Visual Query Builder and Outer Joins

 

 
Powered by phpBB® Forum Software