Maintain left join when selecting from right table 1

[TXP007]

I am using CRXI and SQL Server 2005 tables.

I have 2 tables: Month and Sales.

I am doing a left-outer join (Month ---> Sales).

This allows me to see all months even when there aren't any sales in the month.

I want to maintain my left outer join when I select against a data element from the right table. In other words, I want to show all months, even when my selection result is null.

Does anyone know how to accomplish this?

Thank you for your time,
TXP007

 

[scottostanek]

Yepper.

In the selection criteria test like this

(if not isnull({sales.country}) then {sales.country} ="USA" else true)

gets all the months and sales that are domestic (for me)

It won't really make sense without more info on your tables and fields but that is the gist...

Scotto the Unwise

 

[lbass]

You generally can't just account for nulls, since a field might not be null, but instead of a different value for a particular month.

One solution is to use a command as your datasource, where you add the selection condition in the From clause instead of in the Where clause. This will preserve the left join when there is a selection on the right hand table. For example:

select month.field1, sales.field1
from month left outer join sales on
month.field1 = sales.field1 and
sales.co = 'ABC'

Or you could use a parameter for 'ABC'. With some databases you have to add parens to include both linking clauses as in:

select month.field1, sales.field1
from month left outer join sales on
(
month.field1 = sales.field1 and
sales.co = 'ABC'
)

-LB