OraFAQ Forum: SQL & PL/SQL » Cartesian Join

Home » SQL & PL/SQL » SQL & PL/SQL » Cartesian Join (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cartesian Join [message #404225]

Thu, 21 May 2009 00:48

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