Right outer join problem

[Omavlana]

Hi,

I need your help to resolve this problem. I have written a right outer
join query between 2 indipendent tables as follows.

select b.Account_desc, b.Account, a.CSPL_CSPL from Actual_data_fin al a
right outer join Actual_account_ Tbl b on a.account_desc =
b.account_desc where (a.source_type = 'TY02' or a.source_type is
null) and (a.month = '2ND HALF' or a.month is null) and (a.year = 2004
or a.year is null) and (a.product = 'NP' or a.product is null) order
by b.Sno

But the problem is I have few records in table Actual_account_ Tbl but
do not match the condition "a.account_ desc = b.account_desc" .

As per right outer join, I suppose to get those records as a result of
the above query with null values of a.CSPL_CSPL. But it is not
displaying.

Please help me to resolve this problem.

Regards,
Omav

[David Portas]

Re: Right outer join problem

Could you post some code that actually reproduces the problem you describe
(DDL and sample data INSERTs). I tried the following which appears to work:

CREATE TABLE Actual_data_fin al (cspl_cspl INTEGER NOT NULL, month
VARCHAR(10) NOT NULL, year INTEGER NOT NULL, product CHAR(2) NOT NULL,
source_type VARCHAR(10) NOT NULL, account_desc VARCHAR(10) NOT NULL)

CREATE TABLE Actual_account_ Tbl (account INTEGER NOT NULL, account_desc
VARCHAR(10) NOT NULL, sno INTEGER NOT NULL)

INSERT INTO Actual_account_ Tbl VALUES (123,'ABC',0)

SELECT B.Account_desc, B.Account, A.cspl_cspl
FROM Actual_data_fin al AS A
RIGHT OUTER JOIN Actual_account_ Tbl AS B
ON A.account_desc = B.account_desc
WHERE (A.source_type = 'TY02' OR A.source_type IS NULL)
AND (A.month = '2ND HALF' OR A.month IS NULL)
AND (A.year = 2004 OR A.year IS NULL)
AND (A.product = 'NP' OR A.product IS NULL)
ORDER BY B.sno

Result:

Account_desc Account cspl_cspl
------------ ----------- -----------
ABC 123 NULL

Also, note that you can probably simplify the above query by putting your
WHERE criteria in the ON clause:

SELECT B.Account_desc, B.Account, A.cspl_cspl
FROM Actual_data_fin al AS A
RIGHT OUTER JOIN Actual_account_ Tbl AS B
ON A.account_desc = B.account_desc
AND A.source_type = 'TY02'
AND A.month = '2ND HALF'
AND A.year = 2004
AND A.product = 'NP'
ORDER BY B.sno

--
David Portas
SQL Server MVP
--

[k k]

Re: Right outer join problem

Hi,

Thanks for your help..

The following query has failed to return all the records.

SELECT b.Account_desc, b.Account,
IIf(a.source_ty pe = 'LY01', a.CSPL_CSPL,0), IIf(a.source_ty pe = 'LY01',
a.CSPL_CMS,0), IIf(a.source_ty pe = 'LY01', a.CSPL_CMM,0),
IIf(a.source_ty pe = 'LY01', a.CSPL_CMT,0) from Actual_data_fin al 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.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Re: Right outer join problem

That isn't a MS SQLServer query. TSQL doesn't have an IIF function.

The problem is that you have a WHERE clause referencing the outer table. Put
the WHERE condition in the ON clause:

...
OUTER JOIN Actual_account_ Tbl b
ON a.Account_desc = b.Account_desc
AND a.source_type = 'LY01'

If you need more help then post to the correct group for the product you are
using (Access?).

--
David Portas
SQL Server MVP
--

[Hugo Kornelis]

Re: Right outer join problem

On 28 May 2004 06:46:19 GMT, k k wrote:
[color=blue]
>Hi,
>
>Thanks for your help..
>
>The following query has failed to return all the records.
>
>SELECT b.Account_desc, b.Account,
>IIf(a.source_t ype = 'LY01', a.CSPL_CSPL,0), IIf(a.source_ty pe = 'LY01',
>a.CSPL_CMS,0 ), IIf(a.source_ty pe = 'LY01', a.CSPL_CMM,0),
>IIf(a.source_t ype = 'LY01', a.CSPL_CMT,0) from Actual_data_fin al 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_des c = 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.[/color]

Hi k k,

For right (and left) joins, the place of condition matters. First, the
join is performed. Only the ON clause is checked for the join. Rows that
match are joined, rows from the second table (in case of a right join)
that don't match are added with NULLs as placeholders for the columns from
the first table.

Next, the WHERE clause is applied to the result of the join. Only the rows
in the result set that match the conditions are retained in the output.

In your case, you right joined on equality of account_desc (the
intermediate result had at least 143 rows, maybe more if there are rows in
b that match more than one row in a). Then the WHERE filter for
source_type 'LY01' discarded all rows from b without matching a, as the
outer join set source_type to NULL for these rows. The net result was the
same as an inner join would have been.

Trying to conclude what you wanted from reading your query, I think you'll
have the desired results if you just change "where" to "and". This will
make the LY01 requirement part of the join condition and you're left with
no where clause.


BTW, you're posting in a SQL Server newsgroup, but your use of IIf shows
that you're actually using MS Access. It's best to post to a newsgroup for
the product you're using, since subtle (and some less subtle) differences
between tools can cause big differences.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)