cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
0 Kudos

Add right and full outer joins to JMP Query Builder

JMP Query Builder (Tables > JMP Query Builder) is missing a key functionality, allowing right and full outer joins.  See this techical note:  https://www.jmp.com/support/notes/59/817.html


It would be good to add the ability to do these types of joins with the JMP Query Builder.  This capability does exist, I know, with Tables > Join, but for those who want to use an SQL approach for table joining, this is an important capability to have.

6 Comments
stan_koprowski
Community Manager

Hi @MathStatChem ,

You can already do both types of joins--

A right-outer join is just dependent on the order of the primary table. If you swap tables then you have a right-outer join.

Full-outer join is selecting non-matches from both tables.

 

 Data Wrangling QB join examplesData Wrangling QB join examples

 

Left outer join customer tableLeft outer join customer table

 

Full outer join edit join select non-matches from both tableFull outer join edit join select non-matches from both table

 

Screen Shot 2020-06-29 at 11.15.47 AM.png

 

 

See attached after swap of primary and secondary tables; same results.

Screen Shot 2020-06-29 at 11.18.22 AM.png

 

Inner join matches all records that are common between both tables

Left join matches all records from the left table and the related records from the right table.

Right join matches all the records from the right table and the related records from the left table.

 

 

FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.

MathStatChem
Level VI

You can’t do a full outer join, see the technical note in the OP. I get that you can reverse the tables and do a left join. But that’s not intuitive. Tables > Join has the full left/right/inner/outer joining capability, seems reasonable JMP Query Builder should have this also. 

stan_koprowski
Community Manager

Hi @MathStatChem,

Please accept my apologies I was referring to using JMP Query Builder using database tables and you are referring to using QB for joining of JMP tables.

I should have read the original post more carefully where you do specifically call out JMP QB from the tables menu.

 

cheers,

Stan

 

Ryan_Gilmore
Community Manager
Status changed to: Archived
We are archiving this request. If this is still important please comment with additional details and we will reopen. Thank you!
hogi
Level XII

@stan_koprowski , I wonder what was the reason to disable this feature in Jmp QueryBuilder?

 

 

"Include non-matching rows from 2nd. data table" is greyed out - always. Just to remind the user that this is not possible?

hogi_0-1699558382944.png

 

so, use Tables/Join instead?

... but there are no aggregation options.

 

 

hogi
Level XII

Thanks @Jeff_Perkinson for the link/explanation:Supported Join types for Table > JMP Query Builder 

 

JMP uses SQLite as its database engine for Table > JMP Query Builder. SQLite supports only the following Join types:

  • Inner-Join: returns rows when there is a match in both tables
  • Left-Join: returns all rows from the left table, even if there are no matches in the right table

 

So, it will be hard to close this gap to other competitors.