Let's say we have these tables:
If we join these on Col1, we will produce:
A, London, 2021
A, London, 2022
A, Milano, 2021
A, Milano, 2022
B, Atlanta, 2021
However, this is not accurate representation of the data because it could be that in 2022, A's location was London and in 2021, A's location was Milano!
So the above merge will completely return false results that the event A occurred four times in total, in two different locations and in two different years! While in reality, it could have been one location and two years!
What are your thoughts on this?