473,698 Members | 2,139 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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
Jul 20 '05 #1
4 4849
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
--
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_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!
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_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.


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
6239
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 by an outer join. -- This table stores the answer to each test question CREATE TABLE TestResults ( studentId varchar (15) NOT NULL, testId int NOT NULL REFERENCES Tests(testId), qId int NOT NULL REFERENCES TestQuestions(qId),...
1
1556
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 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...
4
4866
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
10051
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def, serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur where def.TYPEDETABLEDECODES = 4
7
31556
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" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
3
19475
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 have the same type of operator, without using the OUTER JOIN syntax ?
0
1239
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 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...
3
17834
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 LEFT OUTER JOIN that result with prospects. (I want to receive all the results of the inner join and any pertinent info from table 3 that is available.) The way it was written in MSSQL was basically..
9
11918
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: http://www.codinghorror.com/blog/archives/000976.html
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8856
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6515
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5858
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4360
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4613
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2321
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1997
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.