Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Full outer join giving only left outer join

Posted on 2004-03-28
9
Medium Priority
?
473 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
ID: 10702282
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
ID: 10703725
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
ID: 10710668
That solution does'nt work
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 12

Expert Comment

by:monosodiumg
ID: 10711271
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
ID: 10764131

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
ID: 10766362
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 80 total points
ID: 10777033
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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