Solved

Crystal Reports Table linking Left Outer Join Problem

Posted on 2011-10-01
19
1,242 Views
Last Modified: 2012-05-12
Hi,

I am trying to link 2 tables names Assets and Tests to a table named Results.  I need the links to both be left outer joins, but when I try this I get a message of "If tables are already linked then Join Type can't change."  The links work if the joins are equal.

I need to be able to display data from the two tables on the left regardless of weather there is data in the right (results) table.  The 2 links from the left tables (Assets, Tests) are linked to separate fields in the right (results) table.  

Please let me know if more info is needed.  Thanks in advance.
0
Comment
Question by:jjg1576
  • 9
  • 8
  • 2
19 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36897486
Do you have SMART LINKING turned off?
Check FILE --> OPTIONS  or FILE --> REPORT OPTIONS

Are you trying to left join as in
Results LEFT OUTER JOIN Test LEFT OUTER JOIN Assets

Or are Test and Assets the left tables?
If so then delete the links and add them back in.
Make sure smart linking is off

mlmcc
0
 

Author Comment

by:jjg1576
ID: 36897765
mlmcc,

Smart linking is off under File, Options.  Didn't see the option under File, Report Options.  I am trying to link the left tables, tests and assets to Results.  Doesn't allow me to do it either way.  I want to have all results from tests and assets, regardless of weather there is a record in the results table- I'm assuming this means that Test and assets should be the left table.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36897878
Is there another table to the LEFT of tests and assets tables?

I suspect the issue is you have essentially 2 starting points for the query and if you left outer join it won't know which to use or how to link assets to tests.

IS there any link between Assets and tests?

mlmcc
0
 

Author Comment

by:jjg1576
ID: 36897901
mlmcc,

No- there isn't a link between assets and tests although they are both linked to a test parameter table to the right.  There is no table to the left of tests, but to the left of assets there is a location table.

Does this mean that tests needs a table to the left of it?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36898413
No.  The problem is

If Assets has a record but no related record in Result then what record in Test is it associated with?  It will attempt  cross join but some of the records have related records in result so it causes a problem.

You need to rethink the approach.

Perhaps
    Location --> Asset --> Result --> Test

mlmcc

     
0
 

Author Comment

by:jjg1576
ID: 36898431
Mlmcc,

Tests have to be to the left of result because all results derive from a test.  However, some tests do not have results.  Assets are what the tests are attached to (all assets have a certain number of tests, but there can be no test without an asset associated with it.  Assets and Results are linked.  

It goes asset. assetid to result. subjectid and test. testid to result. testid

Location. locationid is linked to asset. locationid

I assume the different fields are the problem here?

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36898563
The problem is you can have assets and tests without results.  If you must have that arrangement then you must use the INNER JOIN.  A Left Join will not be supported in that method.

So long as there is a common result record the report is essentially linking as I stated

Location -->  Asset --> Result --> Test --> SubjectId

or the reverse

What you are trying to do is essentilly

Location -->  Asset --> Result <-- Test <-- SubjectId

What does this mean ( no result record)
Location -->  Asset -->       <-- Test <-- SubjectId

or this ( no TEST for the RESULT)
Location -->  Asset --> Result       Test <-- SubjectId

Crystal cannot handle those cases

mlmcc
0
 

Author Comment

by:jjg1576
ID: 36899935
mlmcc,

Thanks- just to be sure I understand (obviously I struggle with linking concepts), What is the difference between Inner joins and left outer joins?  I know equal joins mean each table must have matching results.  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36900465
A INNER JOIN B
  Return all records in A that have a matching record in B.  Return the matching records from B

A LEFT OUTER JOIN B
   Return All records from A and matching records from B

A
ID    FirstName
1        Tom
2        Dick
3        Peter

B
ID     LastName
1           Thumb
3           Piper

SELECT A.FirstName, B.LastName FROM
A INNER JOIN B ON A.ID = B.ID

Tom Thumb
Peter  Piper

SELECT A.FirstName, B.LastName FROM
A LEFT OUTER JOIN B ON A.ID = B.ID

Tom Thumb
Dick   NULL
Peter  Piper

mlmcc

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:jjg1576
ID: 36901332
mlmcc,

Thanks- that makes sense.  It is actually how I understood it- it's just that I'm trying to distinguish between a test belonging to an asset that has no result vs. that test not belonging to the asset at all.  

I need the report to differentiate between the two.  If the test doesn't exist, then it can say N/A, but if the test exists for that asset and just wasn't taken, I would rather it return blank, or say "Test Missed".

This way the user knows that either the test doesn't exist at all, or in the other case, the test exists, but was missed by the person responsible.  

It may not be possible to differentiate between the two.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36901652
Unless you can relate tests and assets I don't think you can do what you want.

mlmcc
0
 

Author Comment

by:jjg1576
ID: 36903886
mlmcc,

Ok- I feared that might be the case.  I appreciate the prompt feedback!
0
 
LVL 34

Expert Comment

by:James0628
ID: 36908906
There's no link between assets and tests?

 > ... all assets have a certain number of tests, but there
 > can be no test without an asset associated with it.

 That implies a link between assets and tests, and that assets are the "main" table, where each asset may or may not have tests (and each asset and test may or may not have results).

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36909890
I suspect there is an implicit link but it isn't a field in the tables so there is no actual link.  The only link is in the results table.  That is how you handle a many-to-many reltionship.

Test T1 goes with Assets A1, A2, and A3
Asset A1 has Tests T1, T2, T3

That can't be handled in the normal foreign key method but is handled in a separate table
FK Assets   FK Test      related  Data

mlmcc
0
 

Author Comment

by:jjg1576
ID: 36911245
The only link is that there is a test parameters table, which tests and assets are linked to.  Tests become test parameters when they are attached to an asset.  However, even when I link from tests to test parameters and then test parameters to results, it still doesn't let me do what I want to do.  

I suspect it's because if a test isn't taken, there is no way to know it was supposed to be taken.  However, the tests are linked by a uniform time stamp in the report- (it's a cross tab).  So if the test isn't taken, the cell is just blank.  It's possible to put a message in the cell, but there is no way to differentiate between a test that is missed and a test doesn't exist.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 300 total points
ID: 36911833
Sounds like you may need a database change to show scheduled tests and change them to completed or missed.

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 200 total points
ID: 36915968
FWIW, I agree with mlmcc.  It sounds like you need some kind of master list of the scheduled tests, which can then be linked to the Tests and Assets tables.

 James
0
 

Author Comment

by:jjg1576
ID: 36917788
James & mlmcc,

I think that's the solution- thanks for the time and the responses.
0
 

Author Closing Comment

by:jjg1576
ID: 36917841
Solution was accurate, just need intervention from our DBA's which is why this is not an A.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now