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

[SBendBuckeye]

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.

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 join it returns 0 records. I have not had this problem before. The reason I need the second situation is because there are an handful of left join situations which I need to retrieve as well (TableOne with no corresponding TableMany record).

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

 

[synapsevampire]

Some technical information would be useful, such as your version of Crystal and the database/connectivity used.

Set it up with TableOne to TableMany, right click the link and select options and make it a Left Outer.

Please qualify statements such as "The problem is that I can only link from TableMany to TableOne."

Why can you only do this? Does it error? Is it because it returns the wrong data?

For the best results, try posting a mini-spec:

Crystal version
Database/connectivity used
Example data
Expected output.

-k

 

[SBendBuckeye]

Sorry, I put the version in the header instead of in the body of my post. Here is some more information:

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 reecord 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 Key2 which is a numeric CustomerID
6. It does not error, it returns 0 rows

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
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.

Thanks for taking time to respond. Hopefully the additional information was helpful.



Have a great day!

j2consulting@yahoo.com

 

[Turkbear]

Hi,
So make the link
( TableOne TableMany
Key1 ------->Key1
Key2 ------->Key2
)
a left outer join..

Open the Visual Linking Expert ( under the Database menu) and right-click on the link - select left outer as the type..

 

[lyanch]

You are right.. it makes no sense why the second one doesn't work..

Some suggestions..

1. Verify database after changing the links.
2. Check the SQL Crystal is creating for both directions and see if crystal isn't doing something funky in the SQL.
3. Try running the SQL in a separate tool to see if the results are the same (ie is this a crystal problem, or a SQL problem)
4. The ever present.. make sure you have the most recent odbc driver

Lisa

 

[SBendBuckeye]

Turkbear,

The problem I am having is that if TableOne is the left table it returns 0 records no matter how the join is defined.

Lisa,

Here is the actual SQL 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 drivers. I don't have access to SQL tools as I am a contractor.


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

Thanks for your help!

Have a great day!

j2consulting@yahoo.com

 

[Turkbear]

Hi, IN your Sql it shows a INNER ( equal) join

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

You need to specify LEFT OUTER...What is the database being used..Does it support Left Outer Joins?

 

[lyanch]

I see absolutely no reason that both shouldn't return the same dataset (turkbear.. it should return the same without the outer join, with the outer join it should get even more).

Have you tried running the SQL in an ODBC tool outside of crystal? Does the same thing happend?

HAve you tried this in a new blank report, just in case you have some odd suppression kicking in?

Lisa