Join Problems

Hi,
The following query has failed to return all the records.
SELECT b.Account_desc, b.Account,
IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
where a.source_type = 'LY01'
There are total 143 records in Actual_account_Tbl. But the above query
returned only 135 records i.e., only those records satisfy the condition
"a.Account_desc = b.Account_desc" are returned.
As per right outerjoin in the above statement I suppose to get all the
records from table 'b', and blank data from table 'a' if it doesn't
satisfy the condition.
Why it is not consistant?
Pls help me.
Thanks and Regards.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
On Thu, 27 May 2004 23:47:15 -0700, k k wrote:

>Hi,
>
>The following query has failed to return all the records.
>SELECT b.Account_desc, b.Account,
>IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
>a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
>IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
>outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
>where a.source_type = 'LY01'
>There are total 143 records in Actual_account_Tbl. But the above query
>returned only 135 records i.e., only those records satisfy the condition
>"a.Account_desc = b.Account_desc" are returned.
>As per right outerjoin in the above statement I suppose to get all the
>records from table 'b', and blank data from table 'a' if it doesn't
>satisfy the condition.
>Why it is not consistant?
>Pls help me.
>Thanks and Regards.
Hi k k,
See my reply in comp.databases.ms-sqlserver.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)