Need oracle expert - SQL
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