Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Location
    India
    Posts
    40

    Unanswered: To avoid duplicates in joins

    Hi,
    There are two tables.
    One table Tab1 contains cust code,cust details.
    Second table Tab2 holds cust code ,cust name.
    I need to get the cust name for each record in tab1 from tab 2.

    I tried using the following two queries.

    1)SELECT
    CUSTOMER_NO,TERMS_CODE,
    CUST_NAME
    FROM tab1
    LEFT JOIN
    tab2
    ON
    tab1.CUSTOMER_NO=tab2.CUSTOMER_NO

    2)
    SELECT
    a.CUSTOMER_NO,a.TERMS_CODE,b.CUST_NAME
    FROM Tab1 a,
    Tab2 b
    where
    a.CUSTOMER_NO =b.Cust_no

    But both of the queries return duplicate records.
    Can somebody help me to avoid the duplicates and fetch only the rows from the first table tab1?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please can you provide sample output from one or both of the queries please, clearly highlighting your duplicates.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Neena John
    But both of the queries return duplicate records.
    no, they don't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rudy is assuming that you have the appropriate constraints on both tables...
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2008
    Location
    India
    Posts
    40

    To avoid dusplicates in joins

    I want the number of records using the queries
    SELECT COUNT(*)
    FROM tab1
    LEFT JOIN
    tab2
    ON
    tab1.CUSTOMER_NO=tab2.CUSTOMER_NO

    and

    SELECT COUNT(*)
    FROM tab1
    to be same.

    In the second table,there will be one or more records corresponding to a customer code.

    Tab2

    CUST CUST
    CODE NAME
    +------------++------------------------------++++
    DD00 SUV
    GG22 TRUCK PARTS INC.
    AA22 THE SERVICE CENTRE
    AA22 THE SERVICE CENTRE


    So if we do join, it is reading all the records in the second table and showing it in resultset.
    The requirement is that it should read only one

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    What are the key(s) of your tables? If you just want any one name from table 2 then do:

    SELECT
    tab1.CUSTOMER_NO,tab1.TERMS_CODE,
    tab2.CUST_NAME
    FROM tab1
    LEFT JOIN
    (SELECT CUSTOMER_NO, MAX(CUST_NAME) CUST_NAME
    FROM tab2
    GROUP BY CUSTOMER_NO) tab2
    ON
    tab1.CUSTOMER_NO=tab2.CUSTOMER_NO ;

  7. #7
    Join Date
    Oct 2008
    Location
    India
    Posts
    40

    To avoid duplicates in joins

    hi,
    I tried using the query given by u.
    this was the query.

    SELECT
    CUST_NO,COMP_NO,
    CUST_NAME
    FROM Tab1
    LEFT JOIN
    (SELECT OP_CUST_CODE, MAX(CUST_NAME) FROM
    Tab2 GROUP BY OP_CUST_CODE)
    Tab2
    ON
    CUST_NO =OP_CUST_CODE
    WHERE DATE_ADD>=0 AND DATE_ADD<=9305;

    But it is not accepting.
    There are no primary keys in both the tables
    Last edited by Neena John; 07-01-09 at 03:43.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Neena John
    AA22 THE SERVICE CENTRE
    AA22 THE SERVICE CENTRE
    i take back what i said
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2009
    Posts
    1

    let me know if this works

    i have just mentioned the tables as aa and bb, hope you would be able to relate it to your tables

    select custname into #t1 from bb
    where cusid in
    (select aa.cusid from aa inner join (select cusid from bb group by cusid having (count(cusid) =1))bc
    on aa.cusid = bc.cusid)
    group by custname

    create table t2 (custname varchar(10))
    insert into t2(custname)
    select max(custname) from bb
    where cusid in
    (select aa.cusid from aa inner join (select cusid from bb group by cusid having (count(cusid) >1))bd
    on aa.cusid = bd.cusid)

    select * from #t1 union select custname from t2



    Quote Originally Posted by Neena John
    hi,
    I tried using the query given by u.
    this was the query.

    SELECT
    CUST_NO,COMP_NO,
    CUST_NAME
    FROM Tab1
    LEFT JOIN
    (SELECT OP_CUST_CODE, MAX(CUST_NAME) FROM
    Tab2 GROUP BY OP_CUST_CODE)
    Tab2
    ON
    CUST_NO =OP_CUST_CODE
    WHERE DATE_ADD>=0 AND DATE_ADD<=9305;

    But it is not accepting.
    There are no primary keys in both the tables

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Neena John
    There are no primary keys in both the tables
    Then I suggest you fix that (obviously) before you try anything else.

    I can't solve the "not accepting" bit for you. What does "not accepting" actually mean?

  11. #11
    Join Date
    Jun 2009
    Posts
    66
    Why do you have duplicate data in the first place? no unique constraints?

  12. #12
    Join Date
    Apr 2009
    Posts
    15
    This seems extremely confusing, it might be a good idea to script out the tables and then give us some sample data to work with.

  13. #13
    Join Date
    Oct 2009
    Posts
    1
    Try this example, it worked for me and I don't get duplicate email addresses. Not sure if max is specific to MS SQL.

    select e.exhibitorid,name, max(email) as email
    from exhibitors e left join address a on e.exhibitorid=a.exhibitorid
    group by e.exhibitorid,name

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ace8395
    Not sure if max is specific to MS SQL.
    roflmao

    no, it is not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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