Re: Need oracle expert - SQL

On Mar 2, 4:18 pm, marfi95 <marf..._at_yahoo.com> wrote:
> 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

This looks complicated. It would have been helpful if you had
included the DDL and DML:
CREATE TABLE T1(

COMMIT;
First, a test to see if we can fix up table T1 so that the rows that
are related by T1COL are joined together. We will reference the T1
table twice, giving it an alias for the second reference:
SELECT

Now we have a bit of a problem in that we need to join these two
SELECT statements together twice with different join conditions, and
then outer join the results. The first of the two joins looks like
this:
SELECT