SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to combine if i found the two rows are identical in a table?

    Hi all,
    I have one table like company...
    In this table compname is one column..
    User1 enters a company name differently... Ex:TCS
    another user2 enters a company name differently... Ex:Tata Consultancy Services...
    Related products and articles and news are inserted in the respective table with respective company...
    As an admin::
    I found, the two companies are equal but the diff is only compname entered by the user1,user2.
    I want to merge/update the related products,articles and some news related to the company and gives the common name or preferred name to it and update all related tables and gives rights to the authorized user among user1/user2.

    How can i do it..
    If possible give me GUI interface also to do this..
    Please give me suggestions...

    Thanking you..

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by venkat6134 View Post
    How can i do it...
    slowly, carefully, and one related table at a time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your reply...
    I have one table like company...
    In this table compname is one column..
    User1 enters a company name differently... Ex:TCS
    another user2 enters a company name differently... Ex:Tata Consultancy Services...
    As an admin::
    I found, the two companies are equal but the diff is only compname entered by the user1,user2.

    I want to merge/update company and gives the common name or preferred name to it and gives rights to the authorized user among user1/user2.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, i understand

    you will have to do this slowly and carefully, one related table at a time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how can i do without directly interact with database or without login to database panel..
    I want programatically and backend the query will execute and shows result as modified the user for the particular company and company name also..

    Now it is small table, in future it will increase the size of the table data....
    For that reason i want programatically...

  6. #6
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,026
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    @r937 > I think he's asking about how to design a UI for performing the record merge - not the actual database queries involved. With that in mind...

    Create a button called "Merge" and display it to the users with permission to use it on each record. When clicked prompt the user on the next screen to select the record(s) to 'merge into' it. When they click ok on that screen present a third screen that shows all of the records and asks for confirmation.

    At this point you have a Primary record and a group of targets. The information of the primary over rides the targets if there is a conflict. The basic process at this point is to change the foreign keys of the child records of the targets to the id of the primary record that will be receiving the merges.

    The database structure will obviously influence this greatly - and this will be much easier if the database is more or less hierarchical in structure - that is you have parent records with children. In this case you just need to reassign some foreign keys and call it a day.

    I have a patient record system with a merge feature for patient records so for an example I'll walk over how I do it in that system. Patient records have addresses, phone numbers and "episodes of care" for their main child records. The episode of care has a claim table for a child, which in turn has bills, which in turn has procedures and in turn has ledgers items. Ledger items don't have a patient key - to find the patient of a ledger record you have to walk up the tree. That said, it makes merging easier because the number of tables referencing the patient record is minimal.

    When merging patients I throw a warning if the SSN numbers do not match (Note, I would have primary keyed on SSN, but HIPPA law requires that the SSN be encrypted and working with encrypted keys is not ideal, further we don't always have the SSN - we usually do but not always). If the merge goes through the addresses are compared with exact matches discarded. If there's a slight misspelling the user will have to delete the redundant address entry manually. We already have to store multiple addresses since historical addresses must be submitted for historical claims even if the present address is different, so the patient having multiple addresses isn't much of a factor (ditto phone numbers). The primary address and phone is still set by the Primary record of the merge.

    I hope this helps.

  7. #7
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to Michael,
    What i have in mind you represent the same...
    but i want the help to implement it...
    is there any programatic help regarding this topic...
    Give me some ideas on this..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •