Results 1 to 12 of 12
  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
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Sounds like you should have a unique index on one of those tables. But you can always use DISTINCT.

    Dave

  3. #3
    Join Date
    May 2009
    Posts
    258
    The following should do what you need, based on your first query:
    Code:
    SELECT DISTINCT
    CUSTOMER_NO,TERMS_CODE, CUST_NAME 
    FROM tab1 
    LEFT JOIN tab2
    ON tab1.CUSTOMER_NO=tab2.CUSTOMER_NO
    Ax

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Ax,

    yes it will work. But aren't you masking a bigger issue?

    i.e. what Dave said plus table design as I suspect after implementing UK you will loose some records.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm curious, neena john, are you using db2 or sql server?

    --> http://www.dbforums.com/microsoft-sq...tes-joins.html

    in any case, i'll bet the results of your query do ~not~ include duplicate results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    To avoid duplicates in join

    I m using both DB2 and SQL

    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.
    I tried using distinct,but it is skipping many records.
    The problem is that in the second table one or more records corresponding to customer code are present.So it is showing duplicates.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The problem is that in the second table one or more records corresponding to customer code are present.So it is showing duplicates.
    this seems to be a design issue as mentioned in previous posts
    Visit the new-look IDUG Website , register to gain access to the excellent content.

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

    To avoid duplicates in joins

    Is there any way to read only one record (in case of duplicates)from second table using query bcoz we cant do anything regarding design

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Create a view which shows only distinct values and use the view in the join

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Neena John
    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.
    Try the following (or a variant thereof):
    Code:
    SELECT customer_no,terms_code, cust_name
    FROM tab1
    LEFT OUTER JOIN
    (SELECT MIN(cust_name) AS cust_name, customer_no AS c_no
     FROM tab2 GROUP BY customer_no) X
    ON customer_no=c_no
    Note that you possibly loose cust_names (unless the duplicates are really "duplicate" in table tab2).
    Last edited by Peter.Vanroose; 07-01-09 at 04:06.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Peter.Vanroose
    (unless the duplicates are really "duplicate" in table tab2).
    which they are, as revealed in the other thread

    why the mods allow the two threads to coexist is beyoind me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    OK, in that case my query should do the trick.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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