1209643 - Incorrect ANSI-92 Join Syntax for Restricting an Inner Table of an Outer Join

A report is created against a Microsoft SQL Server database through an ODBC connection using the Seagate Crystal Report Designer. Two tables are added to the report and linked using a left outer join.

i.e. TableA -> TableB

Record selection criteria is added to a field in TableB to further filter the data.

Previewing the report only returns records where there is a match between TableA and TableB, giving the appearance of an equal join.

Viewing the SQL query in the report designer through the "Database" menu, "Show SQL Query" option, revealed that the report designer had generated the following query, using ANSI-92 standard join syntax:

select

TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2

from

{ oj TableA LEFT OUTER JOIN TableB ON TableA.Field1 = TableB.Field1 }

where

TableB.Field2 = 'Test'

A left outer join dictates that all records will be returned from TableA, as well as any matching records from TableB. The join should have returned all of the records in TableA, as well as any matching records from TableB where Field2='Test'

Running the same SQL query through Microsoft SQL Server's ISQL_w utility, returned the same records as the Seagate Crystal Report Designer. Microsoft SQL Server's standard join syntax is STAR-EQUAL, but it can also perform joins using ANSI-92 standard join syntax.

Modifying the SQL query in ISQL_w, as follows, to use STAR-EQUAL join syntax returned the correct results:

select

TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2

from

TableA, TableB

where

TableA.Field1 = TableB.Field1 and

TableB.Field2 = 'Test'

According to Microsoft KBase Article Q176480 titled "How to Restrict the Inner Table of an ANSI Outer Join", the correct way to perform record selection on an inner table of an outer join, using ANSI-92 standard join syntax, is in the FROM clause of the SQL query as part of the join. The initial query would have to be modified as follows, in order to return the correct results:

select

TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2

from

{ oj TableA LEFT OUTER JOIN TableB ON TableA.Field1 = TableB.Field1 and

TableB.Field2 = 'Test' }

ODBC ANSI-92 LEFT OUTER JOIN SYNTAX Seagate Crystal Reports Connecting via ODBC MS SQL Server Incorrect Join/SQL Syntax , c2003310 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed