Re: Problem in comparison of two way records in table

kalgill_at_talk21.com wrote:
> Hi All,
>
> I have a problem with a table whereby everytime a record is created
> another is created showing the opposite of this:
>
> e.g.
>
> Rel_Id (PK)|Source_Id|To_Id|Start_Date|End Date
> R1 |P1 |P2 |2005-01-05|2005-02-05
> R3 |P2 |P1 |2005-01-05|2005-02-05
>
> The Rel_Id is a PK but is not nessarily created incrementally.
>
> For some unknown reason in some instances only one of the rows have
> been created either the first or the second.
>
> What I need is to create a SQL script which finds those rows where the
> corresponding opposite row has not been created.
>
> I have tried, nested loops, joins everything I can think of, but maybe
> I am looking this the wrong way.
>
> The oracle environment I am using is (I am at a client site):
> Oracle7 Server Release 7.3.3.0.0 - Production Release
>
> Kind Regards,
>
> Kal

Oracle 7.3.3? My memory isn't good enough to remember much about it though I have an urge to sugget you patch to 7.3.4 (just kidding).

I am really uncomfortable with the idea that any solution is going to remove invalid records and not accidentally delete valid records. How do you define the difference? Your example does not make it clear.

But something like:

INSERT INTO some_table
(rid, col1a, col2a, col1b, col2b)
SELECT rowid, a.col1, a.col2, b.col1, b.col2 FROM mytable a, mytable b
WHERE a.col1 = b.col2

AND a.col2 = b.col1
AND a.start_date = b.start_date
and a.end_date = b.end_date;

or similar

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)