Clever Geek Handbook

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.

+3


source to share


4 answers


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

.

+4


source


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.

+1


source


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
0


source


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.

0


source






More articles:


All Articles