Problem joining 2 tables with a one to many relationship in CR 8.5

[SBendBuckeye]

Hello All,

I have 2 tables, TableOne and TableMany. There is a one to many relationship between TableOne and TableMany. The problem is that I can only link from TableMany to TableOne (eg with TableMany as the left table).

1. Crystal Reports 8.5
2. Connecting to Pervasive2000 database via ODBC
3. TableOne is a customer database
4. TableMany is a detail database with 1 record per customer per month which is identifiable by year and month columns
5. The tables are linked via Key1 which is a string Company Number and Key2 which is a numeric CustomerID
6. It does not error, it returns 0 rows

TableMany TableOne
Key1 ------->Key1
Key2 ------->Key2

This above is an equal join and returns the records I would expect. The problem is that I cannot get them to return any records if I reverse the tables as below.

TableOne TableMany
Key1 ------->Key1
Key2 ------->Key2

With an equal or left join it returns 0 records. I have not had this problem before. The reason I need the second situation is because there are a handful of left join situations which I need to retrieve as well (TableOne with no corresponding TableMany record).

Sample Data
TableOne
Key1 Key2 Name
01 001 John Doe
01 002 Jane Doe
01 003 Sam Hill

TableMany
Key1 Key2 Year Month LTD
01 001 2003 10 100
01 001 2003 11 200
01 001 2003 12 300
01 001 2004 01 400
01 001 2004 02 500
01 001 2004 03 600
01 002 2004 01 50
01 002 2004 02 100
01 002 2004 03 150
01 002 2004 04 200

1. This linkage which is an equal join returns records as below
TableMany TableOne
Key1 ------->Key1
Key2 ------->Key2

Key1 Key2 Name Year Month LTD
01 001 John Doe 2003 10 100
01 001 John Doe 2003 11 200
01 001 John Doe 2003 12 300
01 001 John Doe 2004 01 400
01 001 John Doe 2004 02 500
01 001 John Doe 2004 03 600
01 002 Jane Doe 2004 01 50
01 002 Jane Doe 2004 02 100
01 002 Jane Doe 2004 03 150
01 002 Jane Doe 2004 04 200

2. This linkage, also an equal join returns 0 records
TableOne TableMany
Key1 ------->Key1
Key2 ------->Key2

I need to be able to use Linkage 2 because it ultimately needs to become a left outer join to also pick up Customers who have no corresponding records in TableMany.

Here is the actual SQL Crystal Reports is generating in the two cases noted above. I stripped it down so less stuff to dig through. I verified the Database and asked Tech Support about the ODBC drivers. I don't have access to SQL tools as I am a contractor so I cannot verify the SQL outside of Crystal Reports

Returns 0 Records when TableOne is the left table

SELECT
LeaseDatabase."LeaseCompanyNum",
LeaseDatabase."LeaseNum",
LeaseCapitalEarnings."EarningCompanyNum",
LeaseCapitalEarnings."EarningLeaseNum",
LeaseCapitalEarnings."EarningYear",
LeaseCapitalEarnings."EarningMonth",
LeaseCapitalEarnings."EarningLtdEarning"
FROM
"LP0"."LeaseDatabase" LeaseDatabase INNER JOIN
"LP0"."LeaseCapitalEarnings" LeaseCapitalEarnings ON
LeaseDatabase."LeaseCompanyNum" =
LeaseCapitalEarnings."EarningCompanyNum"
AND
LeaseDatabase."LeaseNum" =
LeaseCapitalEarnings."EarningLeaseNum"
ORDER BY
LeaseDatabase."LeaseCompanyNum" ASC,
LeaseDatabase."LeaseNum" ASC

Returns Records if TableOne is the right table

SELECT
LeaseCapitalEarnings."EarningCompanyNum",
LeaseCapitalEarnings."EarningLeaseNum",
LeaseCapitalEarnings."EarningYear",
LeaseCapitalEarnings."EarningMonth",
LeaseCapitalEarnings."EarningLtdEarning",
LeaseDatabase."LeaseCompanyNum",
LeaseDatabase."LeaseNum"
FROM
"LP0"."LeaseCapitalEarnings" LeaseCapitalEarnings
INNER JOIN "LP0"."LeaseDatabase" LeaseDatabase ON
LeaseCapitalEarnings."EarningCompanyNum" =
LeaseDatabase."LeaseCompanyNum"
AND
LeaseCapitalEarnings."EarningLeaseNum" =
LeaseDatabase."LeaseNum"
ORDER BY
LeaseDatabase."LeaseCompanyNum" ASC,
LeaseDatabase."LeaseNum" ASC

I can do a Union query but I want to avoid that if possible since Crystal doesn't make that easy for future maintenance.

Thanks for any ideas and/or suggestions!

Have a great day!

j2consulting@yahoo.com