Re: differences

Nis Jorgensen <nis_at_dkik.dk> wrote in message news:<n1df5ugnqkpuiapvb5fhu392vo117c38at_at_4ax.com>...
> On 29 Jan 2002 09:44:13 -0800, mikharakiri_at_yahoo.com (Mikito Harakiri)
> wrote:
>
> >table A (
> > position NUMBER,
> > textline VARCHAR2
> >);
> >
> >table B (
> > position NUMBER,
> > textline VARCHAR2
> >);
> >
> >what is a SQL query that returns all the differences between A and B?
> >It is important that position is used for ordering only, so that it is
> >pretty much like I'm comparing 2 text files. UNIX diff utility does
> >exactly that, but query must be superior, right?-)
>
> Define EXACTLY what you want as the output - then we might be able to
> tell you how to get it.
>
> For instance:
>
> Insert into A values ((1, 'Text1'), (2, 'Text2'));
>
> Insert into B values ((1, 'Text2'),(2, 'Text1')
>
> Which result set do you want?

> cat 1

Text1
Text2
> cat 2

Text2
Text1
> diff 1 2

1d0
< Text1
2a2
> Text1
>

Therefore, the expected output is:

position textline1 textline2

       1       Text1        null
       2       Text2       Text2
       3        null       Text1

Ambiguity like this makes it difficult to express it in SQL, I guess. So lets eliminate ambiguity by adding one more column:

solution position textline1 textline2

       1          1       Text1        null
       1          2       Text2       Text2
       1          3        null       Text1
       2          1        null       Text2
       2          2       Text1       Text1
       2          3       Text2        null

> No matter what you end up wanting, don't expect it to be easy in SQL
> (put probably possible).
Received on Wed Jan 30 2002 - 18:41:24 CET