Hi,

Below is SQL Statment: I am running on SQL Server 2000 MSDE version on
Windows2k Professional.

For some reason, this query doesn't return the unmatched rows in an
OuterJoin query.... it always return only the matched rows...

SELECT
dbo.DLY_REST_CUSTCNT.mlprdcd AS rest_mealcode,
dbo.DLY_REST_CUSTCNT.restno,
dbo.DLY_REST_CUSTCNT.saledt,
dbo.DLY_REST_CUSTCNT.custcnt,
dbo.FA_MLPRD.descrip,
dbo.FA_MLPRD.mlprdcd,
dbo.FA_MLPRD.sortord,
dbo.FA_MLPRD.comment

FROM dbo.DLY_REST_CUSTCNT RIGHT OUTER JOIN
dbo.FA_MLPRD ON dbo.DLY_REST_CUSTCNT.mlprdcd =
dbo.FA_MLPRD.mlprdcd

WHERE (dbo.DLY_REST_CUSTCNT.restno = '0052') AND
(dbo.DLY_REST_CUSTCNT.saledt = CONVERT(DATETIME, '2003-07-01 00:00:00',
102))


Any suggestion is appreciated...

Jean

Your query will only return the inner portion of the join because you have a
WHERE clause that references the left table (DLY_REST_CUSTCNT). If you want
to include non-matched rows, make your WHERE criteria part of the ON clause:

....
ON dbo.DLY_REST_CUSTCNT.mlprdcd = dbo.FA_MLPRD.mlprdcd
AND dbo.DLY_REST_CUSTCNT.restno = '0052'
AND dbo.DLY_REST_CUSTCNT.saledt = '20030701'

--
David Portas
------------
Please reply only to the newsgroup
--

Thanks, David.


[quoted text, click to view]