[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1550
  • Last Modified:

Crystal Reports Table linking Left Outer Join Problem

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
jjg1576
Asked:
jjg1576
  • 9
  • 8
  • 2
2 Solutions
 
mlmccCommented:
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
 
jjg1576Author Commented:
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
 
mlmccCommented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
jjg1576Author Commented:
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
 
mlmccCommented:
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
 
jjg1576Author Commented:
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
 
mlmccCommented:
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
 
jjg1576Author Commented:
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
 
mlmccCommented:
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
 
jjg1576Author Commented:
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
 
mlmccCommented:
Unless you can relate tests and assets I don't think you can do what you want.

mlmcc
0
 
jjg1576Author Commented:
mlmcc,

Ok- I feared that might be the case.  I appreciate the prompt feedback!
0
 
James0628Commented:
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
 
mlmccCommented:
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
 
jjg1576Author Commented:
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
 
mlmccCommented:
Sounds like you may need a database change to show scheduled tests and change them to completed or missed.

mlmcc
0
 
James0628Commented:
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
 
jjg1576Author Commented:
James & mlmcc,

I think that's the solution- thanks for the time and the responses.
0
 
jjg1576Author Commented:
Solution was accurate, just need intervention from our DBA's which is why this is not an A.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now