Home » SQL & PL/SQL » SQL & PL/SQL » Left Outer join, eliminate extra records
Left Outer join, eliminate extra records [message #409036] Thu, 18 June 2009 19:30 Go to next message
nidi_03
Messages: 10
Registered: May 2009
Location: US
Junior Member
Hi!
I have to fetch data from a left outer join of 2 tables say app(count = 11432) and lkp(count=86). The where clause condition that I am using is fetching several extra records(49034).How can I eliminate the extra records?Pls suggest.
my query is

select distinct tab1.EXTRACT,
tab1.ACTION,
tab1.PLATFORM,
tab1.APPL,
tab1.CUSTID,
tab1.STARTDATE,
tab1.ENDDATE,
tab2.MAJOR,
tab1.IND,
tab1.FSA,
tab2.SOLD,
tab1.SRCID,
tab1.DIV,
tab1.TKEY,
tab1.FT,
tab1.VOID,
tab1.MINOR
from app tab1,
(select distinct PLATFORM,
MINOR,
policy,
MK,
IND,
MAJOR,
SOLD
from LKP) tab2
where tab1.PLATFORM = tab2.PLATFORM(+)
and tab1.policy = tab2.policy(+)
and decode(tab1.PLATFORM,
'X',
tab1.LINE,
tab1.MINOR) = tab2.MINOR(+)
Re: Left Outer join, eliminate extra records [message #409037 is a reply to message #409036] Thu, 18 June 2009 19:42 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Why do you NOT follow Posting Guidelines?
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Why is this posted under Server Utilities & not SQL sub-forum?

SELECT DISTINCT tab1.EXTRACT,
                tab1.ACTION,
                tab1.platform,
                tab1.appl,
                tab1.custid,
                tab1.startdate,
                tab1.enddate,
                tab2.major,
                tab1.ind,
                tab1.fsa,
                tab2.sold,
                tab1.srcid,
                tab1.div,
                tab1.tkey,
                tab1.ft,
                tab1.void,
                tab1.minor
FROM   app tab1,
       (SELECT DISTINCT platform,
                        minor,
                        policy,
                        mk,
                        ind,
                        major,
                        sold
        FROM   lkp) tab2
WHERE  tab1.platform = tab2.platform (+)
       AND tab1.policy = tab2.policy (+)
       AND Decode(tab1.platform,'X',tab1.line,
                                tab1.minor) = tab2.minor (+) 


>How can I eliminate the extra records?
We don't know what you consider to be "extra records"

>Pls suggest.
We do not know what expected/desired results should be.
If you require a different result set, the change the SQL as needed or desired.

[Updated on: Thu, 18 June 2009 19:47]

Report message to a moderator

Re: Left Outer join, eliminate extra records [message #409068 is a reply to message #409036] Fri, 19 June 2009 02:47 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
nidi_03 wrote on Fri, 19 June 2009 02:30
Hi!
I have to fetch data from a left outer join of 2 tables say app(count = 11432) and lkp(count=86). The where clause condition that I am using is fetching several extra records(49034).How can I eliminate the extra records?Pls suggest.

Firstly you shall understand, why there are more records in the resultset than in both tables. The most obvious reason is, that there are multiple rows with the same values in the join columns/expressions in both tables.

So, e.g., if there are 10 rows in TAB1 with (PLATFORM, policy, decode(PLATFORM,'X',LINE,MINOR)) = ('X','auto','1.0')
and 5 rows in TAB2 with (PLATFORM, policy,MINOR) = ('X','auto','1.0')
the resultset will contain 50 rows. To get 'only' 10 rows, either add another join condition which will distinguish these duplicate rows, or delete additional rows with the same values in join columns from data. But, maybe the query result is correct and there is nothing to be done.

Of course it depends on table definitions (primary/foreign keys and logic behind), which you did not provide. So, good luck.
Previous Topic: Send sql query output to file or send by mail
Next Topic: partition by
Goto Forum:
  


Current Time: Wed Dec 07 02:58:33 CST 2016

Total time taken to generate the page: 0.19953 seconds