Left Outer Joins not returning records in Right Table.

[CRCRCR]

Hi.

I have a report with 3 Tables

SAMPLE, TEST and RESULT.

Samples have Tests and Tests have Results. "One to many" In each case.

To hopefully illustrate my problem, say I have a SAMPLE which has the Weight TEST, which has Start, Finish and Difference RESULTS.

The report works as expected in this Case.

However if there are TESTs with no RESULTs yet, no RESULT records are created in the RESULT table yet. So therefor, to retrieve these SAMPLEs with Weight TESTs, left outer joins are required.

The report works as expected in this Case.

The problem, is, when there are records in the RESULT table, I need to stop certain Records. For example in the above example, I need to stop those "Start" and "Finish" Results from being returned. I still need the "Difference results. I also still need to return those SAMPLES and TESTS even when there are no RESULTS. Once I start to have records selection criteria (on the RESULT Table, the Right Table) it ignores the Left Join.

Is this possible, to have left Joins and selction criteria on the Right Table

Hope this is clear.

I could do the left outer join, and suppress unwanted RESULT records, but I need to do summary operations on the results and also export it to Excel. I find these suppressed records are just hidden from view, rather than actually suppressed, and get caught up in calcs and exports.

Cheers, Kai

 

[Madawc]

Formulas in Crystal stop when they hit a null, unless they first have an IsNull test. For left joins, the fields from the right-hand table will contain nulls. Allow for this.

If that doesn't work - I've had inconsistent results with such tests - you could include the records and suppress them in detail-line sections. Awkward but it gets the right output.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

What version of CR are you using? There is a solution if you have 9.0 or above. Please identify how your tables are currently joined--what fields from what tables are joined.

-LB

 

[CRCRCR]

CRXI

SAMPLE --> TEST --> RESULT.

Result table may not always have records.

Returning amongst others, SAMPLE,Number and TEST.Analysis fields, RESULT.Name and RESULT.Value

However, I wish to stop records with RESULT.Name = "Start" and "Finish" from being returned.

So in the record selection, from memory, RESULT.Name 'not one of' Start and Finish

Which generates something similar to AND RESULT.Name NOT IN
("Start", Finish")

(Not 100% sure about this, maybe something like)

AND NOT RESULT.Name IN ("Start", Finish"

I'd rather have these records not be returned, rather than suppressing them later on.

Cheers, Kai

 

[lbass]

You didn't say what fields are joined, so I'll just make them up in the sample query below.

Start a new report->database expert->above the table lists->add command and enter the SQL Query directly like this:

Select sample.`number`, test.`analsis`, result.`name`, result.`value`
from sample inner join test on
sample.`number` = test.`number`
left outer join result on
(test.`number` = result.`number` and
result.`value` not in ('Start','Finish'))

The punctuation would be specific to your datasource, so you might want to check the Show SQL Query on your current report to see how to use the punctuation.

-LB

 

[cmmrfrds]

As was pointed out, check for null records in the results table. If the results table is on the right side of the join, when the equal condition is not true, there will be null records returned. Catch these in your record selection with an isnull test. Assuming the primary id on the result table is resultid.

For example.
(test.testid = result.testid and etc.........)
or isnull(result.resultid)

 

[CRCRCR]

Thanks to all who replied and helped me out with this.

I went the NULL test route, basically, because I was a bit loathe to change the SQL myself, what with parameters etc..

Kai.

For reference, the final Record selection formula became:

{V_SAMPLE_ALL.SAMPLING_POINT} startswith "RE" and
{V_SAMPLE_ALL.SAMPLED_DATE} = {?sampled Date} and
not ({V_SAMPLE_ALL.STATUS} in ["X", "U"]) and
{V_TEST_ALL.STATUS} <> "X" and
{V_TEST_ALL.ANALYSIS} = "MWI_D_MN" and
NOT isnull ({V_RESULT_ALL.Name}) and
NOT ({V_RESULT_ALL.NAME} in ["Comments", "Alpha", "RSD", "SD"]) and
not ({V_SAMPLE_ALL.SAMPLE_TYPE} in ["BLANK", "CONTROL", "DUPL1", "DUPL2", "DUPLICATE"])


The
"NOT isnull ({V_RESULT_ALL.Name})"
allowed the
"NOT ({V_RESULT_ALL.NAME} in ["Comments", "Alpha", "RSD", "SD"])"
to work as expected.


The resultant SQL became :


SELECT "V_SAMPLE_ALL"."ID_NUMERIC", "V_SAMPLE_ALL"."SAMPLED_DATE",
"V_TEST_ALL"."ANALYSIS", "V_SAMPLE_ALL"."SAMPLING_POINT",
"V_SAMPLE_ALL"."STATUS", "V_RESULT_ALL"."TEXT",
"SAMPLE_POINT"."REPORTING_ZONE", "V_RESULT_ALL"."NAME",
"V_TEST_ALL"."STATUS", "V_SAMPLE_ALL"."SAMPLE_TYPE"
FROM (("LIMSENQ"."V_SAMPLE_ALL" "V_SAMPLE_ALL" LEFT OUTER JOIN
"LIMSENQ"."V_TEST_ALL" "V_TEST_ALL" ON
"V_SAMPLE_ALL"."ID_NUMERIC"="V_TEST_ALL"."SAMPLE") INNER JOIN
"VGSM"."SAMPLE_POINT" "SAMPLE_POINT" ON
"V_SAMPLE_ALL"."SAMPLING_POINT"="SAMPLE_POINT"."IDENTITY") LEFT OUTER JOIN
"LIMSENQ"."V_RESULT_ALL" "V_RESULT_ALL" ON
"V_TEST_ALL"."TEST_NUMBER"="V_RESULT_ALL"."TEST_NUMBER"
WHERE "V_SAMPLE_ALL"."SAMPLING_POINT" LIKE 'RE%' AND
("V_SAMPLE_ALL"."SAMPLED_DATE">={ts '2007-04-14 00:00:00'} AND
"V_SAMPLE_ALL"."SAMPLED_DATE"<{ts '2008-04-15 00:00:00'}) AND NOT
("V_SAMPLE_ALL"."STATUS"='U' OR "V_SAMPLE_ALL"."STATUS"='X') AND
"V_TEST_ALL"."STATUS"<>'X' AND "V_TEST_ALL"."ANALYSIS"='MWI_D_MN' AND
"V_RESULT_ALL"."NAME" IS NOT NULL AND NOT
("V_RESULT_ALL"."NAME"='Alpha' OR "V_RESULT_ALL"."NAME"='Comments' OR
"V_RESULT_ALL"."NAME"='RSD' OR "V_RESULT_ALL"."NAME"='SD') AND NOT
("V_SAMPLE_ALL"."SAMPLE_TYPE"='BLANK' OR
"V_SAMPLE_ALL"."SAMPLE_TYPE"='CONTROL' OR
"V_SAMPLE_ALL"."SAMPLE_TYPE"='DUPL1' OR
"V_SAMPLE_ALL"."SAMPLE_TYPE"='DUPL2' OR
"V_SAMPLE_ALL"."SAMPLE_TYPE"='DUPLICATE')
ORDER BY "V_SAMPLE_ALL"."ID_NUMERIC"


Thanks again, Kai.

 

[CRCRCR]

I've just messed up this page now.

The above record selection, does not
return NULLS. I do require those NULLs
from the RESULTS table.

Back to square one. If the SQL wasn't
so complex, I could try LBASS' way.
Back to square one.

Kai.

 

[cmmrfrds]

I expected the formula to look like this.

{V_SAMPLE_ALL.SAMPLING_POINT} startswith "RE" and
{V_SAMPLE_ALL.SAMPLED_DATE} = {?sampled Date} and
not ({V_SAMPLE_ALL.STATUS} in ["X", "U"]) and
{V_TEST_ALL.STATUS} <> "X" and
{V_TEST_ALL.ANALYSIS} = "MWI_D_MN" and
( isnull ({V_RESULT_ALL.Name}) OR
(NOT ({V_RESULT_ALL.NAME} in ["Comments", "Alpha", "RSD", "SD"]) and
not ({V_SAMPLE_ALL.SAMPLE_TYPE} in ["BLANK", "CONTROL", "DUPL1", "DUPL2", "DUPLICATE"]))

What you had would not bring back the null result records.

 

[CRCRCR]

Thank you so much.

(NOT ({V_RESULT_ALL.NAME} in ["Comments", "Alpha", "RSD", "SD"]))

Had to add an extra ")" to the end of this line.

But Crystal now, returns NULL values from the RIGHT table, AND stops selected RIGHT table records.

Exactly as needed.

Kai.

Just got to figure out for myself, why this works, and why, when I told crystal, it didn't work.