Home » SQL & PL/SQL » SQL & PL/SQL » Cartesian Join (Oracle 10g)
Cartesian Join [message #404225] Thu, 21 May 2009 00:48 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have two tables where I have to find the duplicate records based on some columns.
The below code gives me correct values in which there is only one table called.

select slno, mecode, rid,  testcode, examdate, 
             applno, lifeno
        from(
          select m.slno, m.mecode mecode, m.testcode testcode,
                m.dateofexam examdate, m.applno applno,
                m.lifeno lifeno, rowid rid, row_number() 
                over (partition by m.slno, m.mecode, m.testcode, 
                m.dateofexam, m.applno, m.lifeno
           order by rowid) rn
           from mems_test_details m
      where source = 'LA')
   where rn != 1


The data for the 3 columns slno, applno, RID which I get is as below :-
 slno      applno           RID                      
882300    60665588    AAAuiSAAEAADdGYAAD
882300    60665588    AAAuiSAAEAADdGYAAE
882300    60665588    AAAuiSAAEAADdGYAAF
882600    90010062    AAAuiSAAEAADdGYAAC
882600    90010062    AAAuiSAAEAADdGaAAA



But I have to make a join to another table too to get the required records. When I join to the other table,
I am getting more records than required.
Seems somewhere some cartesian is taking place but not sure where.
select slno, mecode, rid,  testcode, examdate, applno, lifeno
 from(
select m.slno, m.mecode mecode, m.testcode testcode, m.dateofexam examdate, m.applno applno, m.lifeno lifeno,
       m.rowid rid, row_number() over (partition by m.slno, 
m.mecode, m.testcode, m.dateofexam, m.applno, m.lifeno order by m.rowid) rn
      from mems_test_details m, mems_contract c
      where source = 'LA'
       AND m.applno  = c.applno )
      where rn != 1


The data for the 3 columns slno, applno, RID which I get for second query is as below :-
 slno      applno           RID                      
882300    60665588    AAAuiSAAEAADdGYAAD
882300    60665588    AAAuiSAAEAADdGYAAE
882300    60665588    AAAuiSAAEAADdGYAAF
882600    90010062    AAAuiSAAEAADW0bAAC
882600    90010062    AAAuiSAAEAADdGYAAC
882600    90010062    AAAuiSAAEAADdGYAAC
882600    90010062    AAAuiSAAEAADdGaAAA
882600    90010062    AAAuiSAAEAADdGaAAA

I am getting more records in the second case. I should get only 5 records as above.

When I am adding the second table mems_contract c I have only one relation that is m.applno = c.applno.

Please help me in this,

Regards,
Mahi

[Updated on: Thu, 21 May 2009 01:02]

Report message to a moderator

Re: Cartesian Join [message #404232 is a reply to message #404225] Thu, 21 May 2009 01:19 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
The problem is solved....I was getting more data in second case because there was duplicacy in data in second table as well.

Thanks,
Mahi
Previous Topic: Find a value in more than a table
Next Topic: sql select query
Goto Forum:
  


Current Time: Sun Dec 01 12:02:02 CST 2024