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?

Please can you provide sample output from one or both of the queries please, clearly highlighting your duplicates.

no, they don't

Rudy is assuming that you have the appropriate constraints on both tables...

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

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 ;

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

i take back what i said

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

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?

Why do you have duplicate data in the first place? no unique constraints?

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.

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

roflmao

no, it is not