Is joining table completely deceiving?

1

Hello
Let's say we have these tables:

Table1
A, London
A, Milano
B, Atlanta

Table2
A, 2021
A, 2022
B, 2021

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?

2

i hope this is clear ...

image
image760×455 5.4 KB

image
image884×319 5.27 KB

3

As stated - the join is correct and the results are correct. Given only the information available there is no way to determine that a 'location' is related to an 'event' and that an event only occurred in a single location at a given point in time.

This isn't a JOIN problem - it is a modeling problem. The available data does not provide the data needed to get to the results you expect.

2 Likes