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

[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'

[abhilash_kumar]

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