To avoid duplicates in joins

Neena John · #1 (**permalink**) 06-30-09, 08:31

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?

dav1mo · #2 (**permalink**) 06-30-09, 09:13

Sounds like you should have a unique index on one of those tables. But you can always use DISTINCT.

Dave

Ax238 · #3 (**permalink**) 06-30-09, 09:13

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

Cougar8000 · #4 (**permalink**) 06-30-09, 09:45

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.

r937 · #5 (**permalink**) 06-30-09, 10:34

i'm curious, neena john, are you using db2 or sql server?

--> To avoid duplicates in joins

in any case, i'll bet the results of your query do ~not~ include duplicate results

Neena John · #6 (**permalink**) 07-01-09, 01:01

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.

sathyaram_s · #7 (**permalink**) 07-01-09, 01:48

Quote:

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

Neena John · #8 (**permalink**) 07-01-09, 01:56

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

sathyaram_s · #9 (**permalink**) 07-01-09, 02:21

Create a view which shows only distinct values and use the view in the join

Sathyaram

Peter.Vanroose · #10 (**permalink**) 07-01-09, 03:03

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).