Re: Sql query to compare between the tables

ganesh wrote:
> Hi,
>
> My first table has the following snap shot.
> Uid is a GUID
> Since Uid is a GUID , i did INDEXING and thats how i got idx 1,2,3....
>
> dName Addr Uid idx
> --------------------------------------------------
> --------------------------------------------------
> ------------------------------------ -----------
> Valli Surabhi 5D531CB2-E6ED-11D2-B252-00C04F681B71 1
> Valli Surabhi 5D531CB2-E6ED-11D2-B252-00C04F681B72 2
> Valli Surabhi 5D531CB2-E6ED-11D2-B252-00C04F681B73 3
> subhaddep fgeg 5D531CB2-E6ED-11D2-B252-00C04F681B74 4
> Prashh GreenPark 5D531CB2-E6ED-11D2-B252-00C04F681B75 5
> Kriss GardenValley 5D531CB2-E6ED-11D2-B252-00C04F681B76 6
> ABC Mahaveer 5D531CB2-E6ED-11D2-B252-00C04F681B77 7
>
>
> My second table for comparison will be
>
> dName Addr Uid idx
> --------------------------------------------------
> --------------------------------------------------
> ------------------------------------ -----------
> Valli Nilaya 5D531CB2-E6ED-11D2-B252-00C04F681B7A 1
> Valli Surabhi 5D531CB2-E6ED-11D2-B252-00C04F681B7B 2
> Valli Surabhi 5D531CB2-E6ED-11D2-B252-00C04F681B7C 3
> ABC Mahaveer 5D531CB2-E6ED-11D2-B252-00C04F681B7D 4
> Sw Bnagar 5D531CB2-E6ED-11D2-B252-00C04F681B7E 5
>
>
> the output between these two tables must be smethng like this
>
> First table difference with second table
> Valli surabhi
> subhadeep fgeg
> Prashh GreenPark
> Kriss GardenValley
>
>
> second table difference with first table
> Valli Nilaya
> Sw Bnagar
>
>
> my query displayed the following O/p
>
>
> First table with second table
> idx dName Addr
> ----------- --------------------------------------------------
> --------------------------------------------------
> 1 Valli Surabhi
> 4 subhaddep fgeg
> 5 Prashh GreenPark
> 6 Kriss GardenValley
> 7 ABC Mahaveer
>
>
> second table with first table
> idx dName Addr
> ----------- --------------------------------------------------
> --------------------------------------------------
> 1 Valli Nilaya
> 4 ABC Mahaveer
> 5 Sw Bnagar
>
>
> Hope u got the problem???
>
>
>
> My query is smthing like this..
> CREATE PROCEDURE [dbo].[CompareIndexedDatabase]
> AS
> select u.idx,u.dName,u.Addr
> from uidtable u,uidtable1 u1
> where u.idx=u1.idx and
> (
> u.dname<>u1.dname or u.addr<>u1.addr
> )
> UNION
> select u.idx,u.dName,u.Addr
> from uidtable u
> where u.idx not in
> (
> select u1.idx from uidtable1 u1)
> UNION
> select u.idx,u.dName,u.Addr
> from uidtable1 u
> where u.idx not in
> (
> select u1.idx from uidtable u1)
>
> GO
>
> Regards
> Ganesh.k

You are reinventing the wheel. Look up the DBMS_RECTIFIER_DIFF built-in package. A demo can be found in Morgan's Library at www.psoug.org.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org