topic How to merge more than 2 tables? in Discussions

How to merge more than 2 tables?

LargeCamel26038 — Thu, 26 Sep 2024 21:39:09 GMT

I simply want to put the columns from each table in one table. I am able to do this by using join tables with just 2 tables but I want to join 4 tables at once. I have tried to use JMP query builder, but I cannot get it to join them.

Re: How to merge more than 2 tables?

Mark_Zwald — Fri, 27 Sep 2024 03:29:59 GMT

The JMP Query Builder from the Tables menu is how to join more than 2 tables in one step, so you are using the right platform. Why it doesn't work could be due to several factors but usually it would be because you are doing an inner join on columns with no matching values. Try changing the join to a full outer join, that should definitely return a result.

Re: How to merge more than 2 tables?

LargeCamel26038 — Fri, 27 Sep 2024 04:45:07 GMT

Where is the option to do this? There is an option to include non matching rows from the second data table but it is greyed out and I am unable to select it.

Re: How to merge more than 2 tables?

jthi — Sat, 28 Sep 2024 05:39:59 GMT

What type of join do wish to do? Do you have any columns you can join with? Or is it just row by row? If query builder isn't able to do what are looking for, you might have to do this by scripting Join platform (luckily it is simple but still it will require a script). If it is always the same join with the same amount of tables, you can most likely do this with the help of Workflow builder.

@Mark_Zwald do JMP tables support full outer join? https://www.jmp.com/support/help/en/18.0/index.shtml#page/jmp/select-tables-from-a-sql-database.shtml#

To do a full join and import all rows, you would select Include non-matching rows from g1_charges. If only one of the non-matching options is available, the database does not support full joins.

Would be nice if query builder was able to tell what the gray-out means instead of leaving user guessing what they are doing wrong

Re: How to merge more than 2 tables?

hogi — Sun, 29 Sep 2024 08:03:42 GMT

The gray-out "option" can only be activated for Database queries:

When you want to build a query from JMP data tables:

the option is always disabled:

If you need this option you can ask a community manager to reactivate this wish:

... and vote for it.