left outer join not working 1

[VE]

I'm using Crystal 2008 and SQL 2008

I have two tables, one is an assignment table, and one is an invoice table. There are assignments that are not invoiced. I need to create a report that includes all assignments with additional data for assignments which have been invoiced.

I have tried the join in SQL (several different ways with the same results) and in Crystal; I even tried it as a sub report. I expect my results to show all the data for the assignment side with sporadic nulls on the invoice side, but it is acting like an inner join in all cases.

Has anyone run into this? Any idea what could be causing it or what else I could try?

Thank you
VE

 

[lbass]

You should use a left join FROM Assignment TO Invoice, with no selection criteria on the Invoice table fields, since that effectively undoes the left join.

If you need to select based on that table, use a command as your sole datasource, and build the criteria into the FROM clause--this will preserve the left join. Like this:

select table1.field1, table2.field1
from table1
left outer join table2 on
table1.key=table2.key and
table2.date>={?StartDate} and
table2.date<{?EndDate}

Create any parameters within the command screen on the right, and then reference them directly in the command, not in the main report.

-LB

 

[VE]

Hi,

Thank you. That was actually one of the things that I tried and it didn't work. I'm absolutely mystified. I'm about ready to throw them into excel and see if I can do something that way, but I'm sure I'll be asked to do this again.

VE

 

[Turkbear]

Hi,
Can you post the SQL that Crystal created after your joins and selection criteria are done?

Unless your database or connection method does not support Left Outer Joins then a left outer from Assignment to Invoice should work as you want it to.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Please show the command you created in response to my suggestion.

-LB

 

[VE]

Hi,

I must have typed something in wrong because it's working now, thank you all for all your help!

VE