Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Unanswered: Remove duplicates problem on multiple columns.

    Hi everyone. I have a problem where I am wanting to remove duplicates from a union that have two columns that are exactly the same, regardless of the other columns. Below are the two tables I could have. One person "John Doe" can exist in both tables but have a different User_Num. I can do a union but it will leave the duplicate individuals because the rows aren't exactly the same. If I remove the User_Num column then it will remove the duplicate John Doe but i need to keep the User_num field. Any assistance would be appreciated.

    Thanks in advance.

    Code:
    CREATE TABLE T_1(
    User_Num   Number(4),
    User_LName        VARCHAR(10),
    User_FName        VARCHAR(10),
    User_Phone         VARCHAR(10),
    User_Misc           VARCHAR(255));
    
    CREATE TABLE T_2(
    User_Num   Number(4),
    User_LName        VARCHAR(10),
    User_FName        VARCHAR(10),
    User_Phone         VARCHAR(10));
    Last edited by Deeno; 10-20-10 at 15:04. Reason: clarified title.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DDL is a nice start.
    How about some DML to populate tables with sample data?

    Please enumerate expected/desired results & by what logic or rules is result set obtained.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Deeno View Post
    If I remove the User_Num column then it will remove the duplicate John Doe but i need to keep the User_num field.
    As there are multiple different USER_NUM values for one name, the very important question is: which of them do you want to "keep"? Minimal, maximal or based on some formula?
    After you will answer this, you might have a look at aggregate (GROUP BY) queries. It should serve your need.

  4. #4
    Join Date
    Oct 2010
    Posts
    4
    Below is some example data. Based on the information below I want to combine table_1 with table_2 but remove the repeated 'Doe', 'John' even though his user numbers are different (1000 and 2001 respectively).



    Code:
    TABLE_1
    1000, 'Doe', 'John', '5555551212', 'Misc1'
    1001, 'Doe', 'Jane', '5555551313',' Misc2'
    1002, 'Doe', 'Juan', '5555551414', 'Misc3'
    
    
    
    TABLE_2
    2001, 'Doe', 'John', '5555551212'
    2002, 'Rivera', 'Miguel', '5555550123'
    2003, 'Smith', 'Bob', '5555550124'

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to combine table_1 with table_2
    "combine" is not a SQL operation.
    Combine where?
    Combine what?
    Combine based upon what specific criteria?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    select T_1.USER_NUM,T_1.USER_PHONE, T_1.USER_FNAME, T_1.USER_LNAME, T_1.USER_MISC from t_1
    where concat(T_1.USER_FNAME,T_1.USER_LNAME) not in (select concat(T_2.USER_FNAME,T_2.USER_LNAME) from t_2)
    union
    select T_2.USER_NUM,T_2.USER_PHONE, T_2.USER_FNAME, T_2.USER_LNAME, 'x' from t_2
    where concat(T_2.USER_FNAME,T_2.USER_LNAME) not in (select concat(T_1.USER_FNAME,T_1.USER_LNAME) from t_1);
    Thanks and Regards,

    Praveen Pulikunnu

Tags for this Thread

Posting Permissions

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