How to make LEFT JOIN in MS Access without duplicates?
I have 2 tables with duplicate values in one of the columns. I would like to make a left join without taking rows where the mentioned column values are duplicated. For example, I have table X:
id Value
A 2
B 4
C 5
and table Y:
id Value
A 2
A 8
B 2
I am doing LEFT JOIN:
SELECT*
FROM X LEFT JOIN Y ON X.id = Y.id;
I would like to have something like:
id Value
A 2 A 2
B 4 B 2
C 5
so the duplicate identifier (A 8) from table Y is not considered.
You can do it with GROUP BY
:
SELECT X.id, X.value, MIN(Y.value)
FROM X
LEFT JOIN Y ON X.id = Y.id
GROUP BY X.id, X.value
Note that there is no need to add Y.id
to the mix because it is either null
or equal X.id
.
You are looking for GROUP BY to aggregate the records of table Y, effectively rolling them down to one row by one identifier. I chose MIN, but you can use SUM if they are integers, like your example data.
SELECT
x.id , x.Value, y.id, min(y.value)
FROM
X LEFT JOIN Y ON X.id = Y.id
GROUP BY
x.id, x.value, y.id;
I gave exactly what you asked for. But in my opinion, y.Id is not needed in select list and group by list.
These are not what you are asking for, in particular, but if you do not need them, you can use the union.
SELECT * FROM (
SELECT ID, VALUE FROM tableX
UNION ALL
SELECT ID, VALUE FROM TAbleY)
GROUP BY ID, Value
As a result, you will receive
id Value
A 2
A 4
A 8
B 2
B 4
C 5
Hmmm, I think you can do this in Access with correlated subqueries:
select x.*,
(select top 1 y.id
from y
where y.id = x.id
),
(select top 1 y.value
from y
where y.id = x.id
),
from x;
This does not guarantee that the values come from the same line, but in this case it is not very important because y.id
there is (the same as x.id
), or is it NULL
, y.value
proceeds from an arbitrary matching string.