Skip to Content
0

How do I add a condition to a left outer join on the On-clause?

Nov 22, 2016 at 07:49 PM

53

avatar image
Former Member

I wrote a Crystal Report that includes a left outer join to another table. I need to display contents from the driver table and only those values from the second table when there is a match. However, I am not just connecting to the other table...I am adding conditions to include on select records based on a value in a field. I found a post on a Sql Server blog that said that if I place the conditions in the where part of the sql statement it will not include values from the driver table and the solution is to place the conditions on the "on" clause. This is Sql...how can I do this in Crystal Reports?

Wrong: Select t1.aaa, t2.bbb from table1 t1 left outer join table2 t2 on t1.key=t2.key where t2.ccc = 'xyz' and t1.ddd = 'abc'

Correct: Select t1.aaa, t2.bbb from table1 t1 left outer join table2 t2 on t1.key=t2.key and t2.ccc = 'xyz' where

and t1.ddd = 'abc'

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abhilash Kumar
Nov 23, 2016 at 08:29 AM
0

Hi Hector,

You can use the SQL as the datasource of the report instead of relying on CR to generate it for you.

Go to the Database Expert > Connect to the Database > Expand the connection details > Double click 'Add Command' > Paste your SQL query in here.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded