Need oracle expert - SQL

From: marfi95 <marfi95_at_yahoo.com>
Date: Sun, 2 Mar 2008 13:18:27 -0800 (PST)
Message-ID: <a9b5a0ab-4c94-4d66-814c-2c39c21818be@d21g2000prf.googlegroups.com>


All,

I am in need of some expert oracle advice on how to solve the following problem. Any ideas would be most helpful.

What I need to do is find duplicate rows in 2 joined tables (just certain columns are checked for dup, not all) and return information from both tables on a single row. However, my requirement is not just to report the duplicate data, but to report other data from the two tables as well. The problem I run into is that I need to return the data on a single row back to my application in a ref cursor, but table 2 can have multiple rows per table 1 based on the join. So, the original record could have more rows (because of table 2) than the duplicate or the other way. I need to report both back to the user.

Here is an example of the data, what is being used in the dup check and how I need the data returned to my application. I'm open to any ideas, creating temp tables on the fly, etc... One way I thought of was selecting part of the duplicate data into a temp table, then joining that with the other info and pivoting it. I'm hoping there are simpler ways to do this that I'm not thinking of. I'm not an Oracle expert. The other thing I've done is created a view that contains the join of table 1 and 2 already.

Hopefully, this will make sense.

The selection criteria is t1col1, t1col2 from table 1 have to match another row from table 1, but only 1 value (not all) from table2 has to match any value from table 2. It doesn't matter what order they are either as illustrated in the example. I, however, need to report all rows from table 2, even though only 1 matches. Crazy requirements, but thats our user.

Remember table 1 and 2 are joined before the dup check. The base criteria will only use 1 and 2 as the original based on some other data, so that is why 3 and 4 will not show as original records in the example.

Table1:


T1Join1 T1Col1 T1Col2 T1Col3

-------       ----------   ----------    -----------
1            ABC      123         Test
2            DEF      456         Test2
3            ABC      123         Test3
4            DEF      456         Test4


Table2:


T2Join1 T2Col1 T2Col2
-------- --------- -------

1          Fred       XYZ
1          Charlie   YYY
2          Martha   ABC
2          Jane      ABC
3          Fred      CCC
3          Joan      DDD
3          Jack      EEE
3          Rob       EEE
4          Jane      FFF


Result Needed:


T1Join1 T1Col1 T1Col2 T1Col3 T2Col1 T2Col2 T1Join1 T1Col1

T1Col2 T1Col3     T2Col1      T2Col2
----------   ------      ------      ------      -----
------      ------      ------        ------     ------
------          ------
1           ABC     123       Test      Fred      XYZ      3
ABC       123       Test3        Fred        CCC
1           ABC     123       Test      Charlie   YYY     3
ABC        123      Test3        Joan         DDD
1           ABC     123       Test      NULL     NULL    3
ABC        123       Test3        Jack         EEE
1           ABC     123       Test      NULL     NULL    3
ABC        123       Test3        Rob          EEE
2           DEF     456       Test2     Martha  ABC      4
DEF        456       Test4        Jane         FFF
2           DEF     456       Test2     Jane      ABC     4
DEF        456       Test4        NULL        NULL

Hopefully this makes sense and people will take this as a challenge !

Thanks !
Marc Received on Sun Mar 02 2008 - 15:18:27 CST

Original text of this message