How to do a LEFT JOIN in MS Access without duplicates?

Question

I have 2 tables with duplicated values in one of the columns. I'd like to do a left join without taking rows, where mentioned column values duplicates. 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'm doing a LEFT JOIN:

SELECT*
FROM X LEFT JOIN Y ON X.id = Y.id;

Would like to have something like:

id   Value
A   2   A   2
B   4   B   2
C   5

so that duplicated id (A 8) from table Y is not considered.

Answer 1

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 it is not necessary to bring Y.id into the mix, because it is either null or equal to X.id.

Answer 2

You are looking for GROUP BY to aggregate the Y table records, effectively collapsing them down to one row per id. I have chosen MIN but you could 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 have given exactly what you asked for. But in my opinion the y.Id is unnecessary in the select and group by list.