Inner Join returning unexpected results 1

[bdukes]

Greetings:

I have a CR 9.2 that is returning unexpected results. Table A and B are Inner Joined on a common column called CSN. However, when the report executes, it returns records from table A even though there is not a matching record in table B. The report also utilizes several other tables that are Left Outer Joined to table A.

What other information can I provide?

Thanks,
Brent

 

[cmmrfrds]

The left outer join from table A is causing table A to return all the records.

 

[lbass]

Brent,

I agree that an equal join between A and B should return only records in A that match records in B, regardless of whether there are other left outer joins from A to other tables. That is, unless 9.2 behaves differently than 8.0 with respect to joins.

Are you sure that matched records are not being suppressed?

-LB

 

[bdukes]

lbass,

Thanks for the reply. I did confirm that matched records from tables A and B are being include in the resultset (along with those records that do not match).

I'm not aware of any changes in 9.2 that would cause this.

Any other thoughts?

Thanks,
Brent

 

[Turkbear]

Hi,
IIRC,any Left Outer Join from TableA 'tells' Crystal and Oracle to return ALL the records in Table A - That would include those that do not match in TableB - It is the Outer Joins that are returning the 'non-TableB matching' records..

 

[bdukes]

Turkbear,

Thanks for the reply. I just modified the report, and removed those additional tables that were LEFT OUTER JOINED to Table A. This report now only includes Table A and Table B which are Inner Joined on the same column as mentioned previously. I'm still getting records from Table A that are not in Table B. Strange!

The Inner Join in TSQL between these two tables work as expected though, and does not return the result I'm seeing in Crystal Reports.

The database is SQL SVR 2000.

Brent

 

[Turkbear]

Hi , That is very strange..

What database and what driver are you using?
( and I'm assuming a = join when you say Inner )

 

[lbass]

When I mentioned suppression, my thought was that you might have only matched records but that the corresponding field in B might be suppressed so that it APPEARS that you are getting unmatched records, when really there is a matching record.

-LB

 

[kenhamady]

Are you using ODBC or SQL Client to connect?
Can you display the SQL statement and confirm it has an Inner Join?

To confirm that the records are truly unmatched, put the linking field from both tables on the report and see if you get only one filled in. If you are observing any other field from the outer table you could be fooled.

Another test is to add the following rule in the record selection formula:

{tableA.CSN} = {tableB.CSN}

See if the number of records returned to the report changes.

Also, create a new report from scratch using just these two tables and just the linking fields and see if that has the same behavior. Old reports sometimes bring their old drivers with them.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[bdukes]

Thanks all!

After reviewing the SQL Query I found that there wasn't any reference to tableB or the Inner Join that existed between tableA and tableB. When looking at the tables and links within the Database Expert I could see these however. On further review, I noticed where no fields from TableB had been used anywhere in the report. It's appears that when the CR creates the SQL that it removes any references to tables where no columns are used in the actual report.

Brent