1543038 - Incorrect result set returned in Crystal Reports when mixing Left Outer Joins and Inner Joins links

• Incorrect data set returned in Crystal Reports.
• Left Outer Joins act likes an Inner Joins.
• Left Outer Joins doesn’t return all the records from the table on the left.
• In Crystal Reports, when performing a left outer joins from Table A to Table B, and an inner joins from Table B to Table C, the incorrect data set is returned if each table is from different database connection.

• SAP Crystal Reports 2016
• SAP Crystal Reports 2020
• SAP Crystal Reports 2025

1. Create a report in Crystal Reports off one data source and add one table. ( Table A )
2. Add a second table from a different data source. ( Table B )
3. Set a Left Outer Joins Link between Table A to Table B.
4. Add a third table from a different data source. ( Table C )
5. Set an Inner Joins between Table B and Table C.
6. When refreshing the report, it returns the incorrect result set. It performs an inner joins instead of a Left Outer Join between Table A and Table B.

For example:

If we have the following tables in different data source:

Data Source 1	Data Source 2	Data Source 2
Table A	Table B	Table C
1	1	1
2	2	2
3

And if we link Tables as followed:

• Left Outer Joins from Table A to Table B
• Inner Joins from Table B to Table C

Expect the result to be:

Table A	Table B	Table C
1	1	1
2	2	2
3	NULL	NULL

But when refreshing the report, it returns the following in Crystal Reports:

Table A	Table B	Table C
1	1	1
2	2	2

It is missing the row number 3, which we expect to see since there is a Left Outer Joins between Table A and Table C.

• The result set returned is correct based on the table links order.
• When linking tables from multiple data source, it will perform the link starting from the driving table, therefore if we use the example t o illustrate what’s happening, it will:
  • Perform the Left Outer Joins Link between Table A and Table B, which will return:

Table A	Table B
1	1
2	2
3

•  
  • And it will then perform  the Inner Join link between the above result with Table C, and since it is an inner join it will only return the matching records.

Table A	Table B	Table C
1	1	1
2	2	2

 

• Change the order in which the link is performed in order to return the desired result set.

1. Open the report in Crystal Reports, and under the menu: Database, select: Database Expert…
2. In the Database Expert window, under the tab: Links, click on the button: Order Links
3. In the Order Links window, change the order in which the link is performed, and click the button: OK
4. Back to the Database Expert window, click the button: OK, to accept the change.

 Now, when refreshing the report, it will return the desired result set.

For example:

By default the link will be performed in the following order:

• Left Outer Joins from Table A to Table B
• Inner Joins from Table B to Table C

If we change the link order to be:

• Inner Joins between Table B and Table C, then
• Left Outer Joins between the result and Table A

          Then the result set will be:

Table A	Table B	Table C
1	1	1
2	2	2
3	NULL	NULL

Because when the Inner Joins link between Table B and Table C is performed first, it will return:

Table B	Table C
1	1
2	2

 And then the Left Outer Joins will be performed from Table A to the above result, which will return:

Table A	Table B	Table C
1	1	1
2	2	2
3	NULL	NULL

 

inner join left outer join table incorrect result , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2016 ; SAP Crystal Reports 2020 ; SAP Crystal Reports 2025