473,223 Members | 1,762 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,223 software developers and data experts.

Right outer join problem

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_final 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
Jul 20 '05 #1
4 4827
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_final (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_final 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_final 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
--
Jul 20 '05 #2
k k
Hi,

Thanks for your help..

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.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
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
--
Jul 20 '05 #4
On 28 May 2004 06:46:19 GMT, k k wrote:
Hi,

Thanks for your help..

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,

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)
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Guinness Mann | last post by:
In my (admittedly brief) sojurn as an SQL programmer I've often admired "outer joins" in textbooks but never really understood their use. I've finally come across a problem that I think is served...
1
by: Omavlana | last post by:
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...
4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
3
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2...
0
by: K k | last post by:
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...
3
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.