Simple join gives unnecessary rows,How to get join properly with only matches in both tables
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 require开发者_运维知识库d:
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
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
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
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
精彩评论