Simple join gives unnecessary rows,How to get join properly with only matches in both tables

Question

Table 1
Field1 Field2  
AA 20
AA 20
AB 12
AC 13

Table2
field3 field4
AA 20
AA 20
AC 13
AD 23
AW 21

output required:

newfield field2 field4
AA   20 20
AA   20 20
AC   13 13

I used:

 select field1 as newfield, t1.field2,t2.field4
      from table1 t1 join table2 t2 on t1.field1=t2.field3

This does not give the required output,Please let me know how to get the required output,Many thx in advance I cant use distinct as I need the given output, these are not duplicates,other column values are different for these rows

Answer 1

UPDATE: OK, now I think I now what you're trying to do. You want each row in the first table to match with at most one row in the second table. If you are using MS SQL, Oracle or PostgreSQL then you can use ROW_NUMBER to uniquify your rows:

SELECT Field1 AS newfield, Field2, field4
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field2) AS rn
     FROM Table1) AS T1
JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY field3 ORDER BY field4) AS rn
     FROM Table2) AS T2
ON T1.Field1 = T2.field3 AND T1.rn = T2.rn
ORDER BY Field1

Result:

'AA', 20, 20
'AA', 20, 20
'AC', 13, 13

If you are using MySQL you can simulate ROW_NUMBER using variables:

SELECT Field1 AS newfield, Field2, field4
FROM
(SELECT
    Field1,
    Field2,
    @rn := CASE WHEN @last = Field1 THEN @rn + 1 ELSE 1 END AS rn,
    @last := Field1
FROM Table1, (SELECT @rn := 0, @last = NULL) AS vars
ORDER BY Field1) AS T1
JOIN
(SELECT
    Field3,
    Field4,
    @rn := CASE WHEN @last = Field3 THEN @rn + 1 ELSE 1 END AS rn,
    @last := Field3
FROM Table2, (SELECT @rn := 0, @last = NULL) AS vars
ORDER BY Field3) AS T2
ON T1.Field1 = T2.field3 AND T1.rn = T2.rn
ORDER BY Field1

Result:

'AA', 20, 20
'AA', 20, 20
'AC', 13, 13

Answer 2

select * from
(select field1 as newfield, t1.field2,(SELECT DISTINCT field4 from table2 t2 where t1.field1=t2.field3) as field4
      from table1 t1)
      where field4 is not null

Answer 3

I am guessing this is what you are looking for, but your data sample is too small to really tell what is going on:

select field1 as newfield, t1.field2, t2.field4
from table1 t1 
inner join (
    select distinct field3, field4 
    from Table2
) t2 on t1.field1 = t2.field3