Solved

Full outer join giving only left outer join

Posted on 2004-03-28
9
444 Views
Last Modified: 2008-02-01
I Have the following query getting some budgetdetails(gl00201) and the associated actual amounts of that specifc year and date from (gl00110),i have given a fuller outer join on the common keys between the gl00201 and gl00110 ,but still the output am getting is the left outer join,gl00102 is the table from where i am getting the accounts description. kinldy Let me know what is the problem with this query.

SELECT distinct     GL00100.actdescr "ACCTDESC100",  GL00100.actINDX "ACTINDX100",        
GL00102.ACCATDSC "ACCDES102", gl00102.ACCATNUM "ACCAT102",
ISNULL(GL00201.BUDGETAMT,0.00)BUDGETAMT,  gl00201.accatnum "BUDCATNUM",gl00201.ACTINDX "BUDGETIN
D",gl00201.periodid [BudgetPeriodid],gl00201.BUDGETID [Budgetid],
ISNULL(GL10110.PERDBLNC,0.00) CURRENTAMT,GL10110.YEAR1 CURRENTYR,gl10110.accatnum "CURRENTCATNUM",gl10110.ACTINDX "ACTAMTIND",  GL10110.PERIODID ACTPERID
FROM
    (( GL00102 LEFT OUTER JOIN  GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
     LEFT OUTER JOIN GL00201  ON GL00201.ACTINDX=gl00100.ACTINDX and GL00102.ACCATNUM=GL00201.ACCATNUM
     and budgetid ='budget3' and GL00201.periodid Between 0 and 3) FULL OUTER JOIN  GL10110 ON GL00102.ACCATNUM =
GL10110.ACCATNUM and gl10110.year1='2000'
and GL10110.periodid Between 0 and 3 and  gl00201.actindx=gl10110.actindx and gl00201.PERIODID=gl10110.PERIODID)
WHERE
    gl00102.accatnum between 1 and 30
    order by gl00102.accatnum,GL00100.ACTINDX
0
Comment
Question by:AFRINEND
  • 5
  • 2
9 Comments
 
LVL 12

Expert Comment

by:monosodiumg
Comment Utility
The Where clause "gl00102.accatnum between 1 and 30" requires that the gl00102.accatnum  be non-null so it will eliminate any rows on the right hand-side (GL10110) that do not have a corresponding gl00102 row. Put that cluase into the join conditions.



0
 
LVL 12

Expert Comment

by:monosodiumg
Comment Utility
Naz,

In response to your out of band email (stick to the forum please):

...
FROM
    (( GL00102 LEFT OUTER JOIN  GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
     LEFT OUTER JOIN GL00201  ON GL00201.ACTINDX=gl00100.ACTINDX and GL00102.ACCATNUM=GL00201.ACCATNUM
     and budgetid ='budget3' and GL00201.periodid Between 0 and 3) FULL OUTER JOIN  GL10110 ON GL00102.ACCATNUM =
GL10110.ACCATNUM and gl10110.year1='2000'
and GL10110.periodid Between 0 and 3 and  gl00201.actindx=gl10110.actindx and gl00201.PERIODID=gl10110.PERIODID) and gl00102.accatnum between 1 and 30
    order by gl00102.accatnum,GL00100.ACTINDX

0
 

Author Comment

by:AFRINEND
Comment Utility
That solution does'nt work
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 12

Expert Comment

by:monosodiumg
Comment Utility
Can you elaborate?

Try:
...
    (( GL00102 LEFT OUTER JOIN  GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
     LEFT OUTER JOIN GL00201  ON GL00201.ACTINDX=gl00100.ACTINDX and GL00102.ACCATNUM=GL00201.ACCATNUM
     and budgetid ='budget3' and GL00201.periodid Between 0 and 3) FULL OUTER JOIN  (select * from GL10110 where and gl00102.accatnum between 1 and 30 ) Foo ON GL00102.ACCATNUM =
Foo.ACCATNUM and Foo.year1='2000'
and Foo.periodid Between 0 and 3 and  gl00201.actindx=Foo.actindx and gl00201.PERIODID=Foo.PERIODID)
    order by gl00102.accatnum,Foo.ACTINDX

If that doesn't work, please post some sample data.

0
 

Author Comment

by:AFRINEND
Comment Utility

No, the above query doesn't work,i have a zip file with the required data and tables,how can i send that  attachement to you.i did not find any provision to attach files here.

This is quite urgent.
0
 
LVL 12

Expert Comment

by:monosodiumg
Comment Utility
Send me them by email (see my profile) or publish them on a web site or get a yahoo account on which you can publish files.
0
 
LVL 12

Accepted Solution

by:
monosodiumg earned 20 total points
Comment Utility
Naz,
Got the tables.Thx.

You've got a Where clause on GL00102.ACCATNUM so all rows in the resultset will correspond to rows in that table and any joins from that table to other tables might as well be LEFT.Making them FULL makes no difference. It makes sense that if you should only be getting resutls for which you have account descriptions (GL00102) ; I'd assume there are no accounts anywhere for which you do not have a GL00102 record.

What is the relationship between ACTINDX and ACCATNUM?

I'm a bit worried about the fact the so many column reappear in all the tables, e.g. ACTNUMBR_1,_2,_3, PERIODID, DEX_ROW_ID. There may be nothing wrong with that but often when I see the same column over and over again it is becuase there is a normlisation issue.

BTW, the tbls you supplied have NO constraints. Can you supply the DDL for the tables?  Without it I can't even assume that ACCTINDX is unique for GL00100, which it seems to be on the edata sample.

It might be better if you explained in English what your query is supposed to achieve.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now