Comparing two tables

11 Aug 2006 2:05 PM

gv

Hi all,

Trying to compare two tables for Changes and new rows.
I keep getting an error near Where? And is this a correct way of doing this?

SELECT old.*
FROM (SELECT * FROM table1 old
      LEFT OUTER JOIN
       (SELECT * FROM table2) new
        WHERE (CASE WHEN old.col1 = new.col1 and
      old.col2 = new.col2 and
      old.col2 = new.col2 and
      old.col3 = new.col3 THEN 1 ELSE 2 END) = 2

thanks
gv

11 Aug 2006 2:13 PM

Stu

Your syntax needs some work. The following will list all of the rows
in the new table that cannot be matched to rows in the old table (which
is what I interpreted your English question to ask; your SQL is
unclear).

SELECT new.*
FROM Table1 new LEFT JOIN Table2 old ON new.col1 = old.col1
AND
new.col2 = old.col2
AND
new.col3 = old.col3
WHERE old.col1 IS NULL

HTH,
Stu

gv wrote:
Show quote

> Hi all,
>
> Trying to compare two tables for Changes and new rows.
> I keep getting an error near Where? And is this a correct way of doing this?
>
>
> SELECT old.*
> FROM (SELECT * FROM table1 old
>       LEFT OUTER JOIN
>        (SELECT * FROM table2) new
>         WHERE (CASE WHEN old.col1 = new.col1 and
>       old.col2 = new.col2 and
>       old.col2 = new.col2 and
>       old.col3 = new.col3 THEN 1 ELSE 2 END) = 2
>
> thanks
> gv

11 Aug 2006 2:54 PM

gv

Thanks

Actually I had found that on line but couldn't understand what they were
doing.
thanks for clearing it up. Yours makes since reading through what you are
doing.

gv

Show quote

"Stu" <stuart.ainswo***@gmail.com> wrote in message
news:1155305590.515157.205420@74g2000cwt.googlegroups.com...
> Your syntax needs some work. The following will list all of the rows
> in the new table that cannot be matched to rows in the old table (which
> is what I interpreted your English question to ask; your SQL is
> unclear).
>
> SELECT new.*
> FROM Table1 new LEFT JOIN Table2 old ON new.col1 = old.col1
>                                                               AND
> new.col2 = old.col2
>                                                               AND
> new.col3 = old.col3
> WHERE old.col1 IS NULL
>
>
> HTH,
> Stu
>
>
> gv wrote:
>> Hi all,
>>
>> Trying to compare two tables for Changes and new rows.
>> I keep getting an error near Where? And is this a correct way of doing
>> this?
>>
>>
>> SELECT old.*
>> FROM (SELECT * FROM table1 old
>>       LEFT OUTER JOIN
>>        (SELECT * FROM table2) new
>>         WHERE (CASE WHEN old.col1 = new.col1 and
>>       old.col2 = new.col2 and
>>       old.col2 = new.col2 and
>>       old.col3 = new.col3 THEN 1 ELSE 2 END) = 2
>>
>> thanks
>> gv
>

13 Aug 2006 10:21 PM

knightconsulting

for what it's worth you could also try firefly. the output is probably
a little cleaner. it's a free tool i wrote since i had the same
problem.

http://www.getfirefly.net/
(scroll to the bottom)

gv wrote:
Show quote

> Hi all,
>
> Trying to compare two tables for Changes and new rows.
> I keep getting an error near Where? And is this a correct way of doing this?
>
>
> SELECT old.*
> FROM (SELECT * FROM table1 old
>       LEFT OUTER JOIN
>        (SELECT * FROM table2) new
>         WHERE (CASE WHEN old.col1 = new.col1 and
>       old.col2 = new.col2 and
>       old.col2 = new.col2 and
>       old.col3 = new.col3 THEN 1 ELSE 2 END) = 2
>
> thanks
> gv

14 Aug 2006 7:34 AM

jsfromynr

Hi gv,

I think if you are looking at some method ,which can find rows from
table1 that does not exists in table2 and vice-versa.

I think this will help you.

Select col1,col2,col3 from
(
Select 1 X,* from table1
Union All
Select 2 X,* from table2
)X group by col1,col2,col3 having max(X)=1

--- All rows that are in table1 but not in table2 ( A MINUS B)

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com