I am working on Election Department databases in India. I am asked to find the duplicate records of one database with respect to other databases of a state depending on elector name, his guardian name and age. In a state is divided in assembly constituencies and assembly constituencies into polling booth. So my state database has 68 databases as same as no of constituencies. Database name are AC_001, AC_002 so on up to AC_068 and each database contain no of tables depending on no of polling booth in a constituency named as AC001PART001,AC001PART002 so on.... in first database AC_001. A table roughly contain following following same fields -
- ccode( autoincrementation field)
- name of elector
- Relation_type(Father or husband)
- Relation_name(name of guardian)
- Assembly constituency no
- Polling booth no in assembly
- serial no( unique no given to a elector in a pooling booth)
- age
- image of elector
Now I want a query which can generate the duplicate records of one database with respect to other depending on name, relation name and age. I also want the no of times a record repeated or duplicated. Finally I want a list which contain- - Elector name
- Relation_Type
- Relation_name
- Assembly constituency no
- Pooling booth no
- Serial no
- age
- no of times record repeated in both databases
- image of elector
I have already created a query but taking very long to return result. So please suggest the overview of query which can generate required record quickly.
