How JMP handles importing data from tables when there are repeat values between tables
Created:
| Last Modified:
At my January 2017 webcast, , an attendee asked how JMP handles joining data from tables where there are multiple matches to the variable used for the join. The example he gave was: A student took the SAT twice, and you're interested in both of those results but they aren't distinguished by anything more than the row order (no column mentioning repeat value).
I discussed the question with JMP developer Eric Hill who explained:
In SQL joins, all matching rows are always present in the result. This is true for 1 to 1, 1 to many and many to many scenarios. Consider these two tables:
A SQL join on these tables on “t1.Name = t2.Name” is going to match each Bob from table 1 with each Bob from table 2, and the same for Mary, resulting in an 8-row table:
You’ll get at least those 8 rows regardless of join type (inner, left outer, right outer, full outer) because “outer” joins are only about what to do with *non-matching* rows.
The harder thing from a SQL perspective is if you for some reason only wanted the *first* matching row from table 2 rather than all matching rows. That generally requires a nested SELECT, which you’d need to use custom SQL to achieve with Query Builder.