Re: Sql query to compare between the tables

On Nov 2, 9:26 am, "Carlos" <miotromailcar..._at_netscape.net> wrote:
> 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.kYou may take a look at MINUS clause.
>
> HTH
>
> Cheers
>
> Carlos.- Hide quoted text -- Show quoted text -

Use of the MINUS set operator is one way and here is another http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html

HTH -- Mark D Powell -- Received on Thu Nov 02 2006 - 09:52:52 CST