Skip to Content
0
Former Member
Nov 22, 2016 at 07:49 PM

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

473 Views

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'