Hi,
I have a problem in comparing a set of records of same schema.
Actually I have a set of records in a table having same ID n I have to find the variance between the fields.


Please reply me if U have some idea in this matter..





Thanks and Regards,
Jhansi

__________________
Thanks and Regards,
Jhansi

You should always post your table structure and required output.
Anyway if you want to query one table more than once, then you can do it in following manner.
Consider following table EMPLOYEE, col MNGRID is self referencing col EMPID

PK->EMPID
COL->EMPNAME
FK->MNGRID


SELECT A.EMPID,A.EMPNAME,A.MNGRID,B.EMPNAME MANGERNAME
FROM EMPLOYEE A
INNER JOIN EMPLOYEE B ON (A.MNGRID=B.EMPID)



urt

lets a table having fields..EmpID,City,PhoneNumber
this table contains a set of records for the same EmpID(EmpID is not a primary key here) with different city and PhoneNumber.In this case I have to identify the variance in each record.

for example the following is the recordset
EmpID City PhoneNumber ID
==== ====== ============ ===
123 Blore 080123456 1
123 Chennai 044123467 2
123 Blore 080123445 3

Now I need the variance in each record..
the output should come like this...

fieldName UpdatedFrom UpdatedTo
========= =========== =========
City Blore Chennai
PhoneNumber 080123456 044123467
City Chennai Blore
PhoneNumber 044123467 080123445


If U have some idea please do send asap.

Thanks and Regards,
Jhansi

I am Assuming that your for empid 123 there are id's with 1,2, 3. same way if empid 150 has

5 records then their ids are expected to numbered as 1,2,3,4,5.

Following query works on above assumption other wise you have to use cursor and scan each

row one by one.

SELECT A.EMPID,A.CITY AS ORGCITY, B.CITY AS NEWCITY,A.PHONENUMBER AS ORGPHONENUMBER,
B.PHONUMBER AS NEWPHONENUMBER
FROM EMPTABLE A
INNER JOIN EMPTABLE B ON A.EMPID=B.EMPID AND A.ID+1=B.ID
WHERE A.CITY<>B.CITY OR A.PHONENUMBER<>B.PHONENUMBER

IF INFORMATIOIN IS CHANGED,
EMPID WITH MAXID WILL NOT COME AND UNCHANGED ROW WILL NOT COME
 OUTPUT WILL BE FOLLOWING
EMPID ORGCITY NEWCITY ORGPHONENUMBER NEWPHONENUMBER
123 BLORE CHENNAI 080123456 044123467
123 CHENNAI BLORE 044123467 080123445

urt