Left Outer Join Criteria on Parent Table

[mattdrinks]

I am using Crystal Reports XI and have two tables joined by a left outer join.

Quick sample data:

Table A       Table B
Field1       Field1  Field2
1000          1000    5.00
2000          1000    6.00
3000          1000    4.50
4000          3000    10.50
5000          5000    11.25

I can make a quick report something like this:

1000          1000    5.00
1000          1000    6.00
1000          1000    4.50
2000
3000          3000    10.50
4000
5000          5000    11.25

But when I use the Select Expert to add some criteria to Table A (eg. Field 1 =>2000) then the null records are no longer shown.
Report displays:

3000          3000    10.50
5000          5000    11.25

I understand that if the criteria is based on a field in Table B then I need something like:

IsNull(Field2) or Field2 = "10.00"

But my criteria is based on a field from Table A.
Does anyone know how I can get the Null records to display when I have selection based on a field from Table A?

Matt.

 

[lbass]

It sounds like you might have the join backwards. The left join should be FROM table A to table B.

-LB

 

[mattdrinks]

I have checked this and I have the join FROM table A to table B.

I have managed to solve the problem by adding a Formula field with the following formula

if {TableA.Field1} >="3000" then "Yes" else "No"

and then added the following select criteria using the Select Expert.
{@CUSTOM_FIELD} = "Yes"

This produces the required results, why it does not work the original way I am not sure. My thinking is that it is because the Selection criteria is pushed down to the server, where as using a formula field forces CR to perform the Selection once it has the data.

Thanks for your suggestion (I did change the join just to make sure but it was right as I had it originally)

Matt

 

[AngelB]

Hi there

Try including the criteria in the join syntax itself e.g.

SELECT {field_list}
FROM TABLE A LEFT OUTER JOIN TABLE B
ON {normal_linking_criteria}
AND Field1 >= 2000

Hope this helps


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[AngelB]

Please excuse my ignorance, I thought I was in a SQL forum for a minute! Is it possible for you to create a view based on these two tables outside of Crystal? Then you could use the syntax I suggested.

Thanks (and sorry)


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[synapsevampire]

I think LB was correct here, but you should be posting what is in the Database->Show SQL Query and everything in the record selection for us to verify.

btw, Geraints suggestion is worth noting, instead use a Command Object and paste in proper SQL if possible.

-k