How JMP handles importing data from tables when there are repeat values between tables
Jan 20, 2017 7:32 AM
| Last Modified: Aug 18, 2020 11:15 AM
At my January 2017 webcast, Organizing and Getting the Most from JMP Tables, 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) By the way, I am presenting the webcast again on June 2, 2017.
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.