<anonymous@discussions.microsoft.com> wrote:

>I'm having a problem understanding why my left outer join
>in a crystal reports isn't returning expected results.
>According to documentation in crystal and sql a left outer
>join will return all the records in the left table
>regardless if there is or isn't a match in the right
>table. Below is the sql statement cut and pasted from my
>crystal report. If it helps any better I am hitting Great
>Plains Dynamics 7.5 database tables for inventory master
>against sales transaction header history and sales
>transaction amounts history. The only reason I know the
>left outer isn't working properly is there is about 6
>items not showing on the report. There are no orders in
>history for this item. That is why I'm using the left
>outer join,,so that it will display the item number on the
>report even if it doesn't have an order history.
>
>SELECT
> IV00101.ITEMNMBR, IV00101.USCATVLS_6,
> SOP30300.SOPTYPE, SOP30300.SOPNUMBE,
> SOP30200.DOCDATE
>FROM
> { oj (CEC.dbo.IV00101 IV00101 LEFT OUTER JOIN
>CEC.dbo.SOP30300 SOP30300 ON
> IV00101.ITEMNMBR = SOP30300.ITEMNMBR)
> INNER JOIN CEC.dbo.SOP30200 SOP30200 ON
> SOP30300.SOPTYPE = SOP30200.SOPTYPE AND
> SOP30300.SOPNUMBE = SOP30200.SOPNUMBE}
>WHERE
> (IV00101.USCATVLS_6 = 'POS-DSP' OR
> IV00101.USCATVLS_6 = 'POS') AND
> SOP30300.SOPTYPE = 2
>ORDER BY
> IV00101.ITEMNMBR ASC
>