cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Pacco
Level III

multiple join

Hey, I am trying to join multiple tables and copied the SQL script from the JMP Query Builder into a script, adding some commands in the beginning. Probably, it doesn´t work as it´s SQL and not JSL language?

 

My problem is that in JMP Query Builder I do not have the option to decide which columns should be chosen for the matching of the rows. Anybody, who can help me out here?

 

This is the script I now have. (the combination of JSL and SQL)

dt=CurrentDataTable ();
dt<< SELECT * 
FROM "mon_trend Abs._OF 420nm 5cm"  t1 
	LEFT OUTER JOIN "mon_trend TOC mg l" t20 
		ON  (  ( t20.Site = t1.Site )  AND  ( t20.Season = t1.Season ) )
	LEFT OUTER JOIN "mon_trend Syrgashalt mg l" t19 
		ON  (  ( t19.Site = t1.Site )  AND  ( t19.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Si mg l" t18 
		ON  (  ( t18.Site = t1.Site )  AND  ( t18.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend PO4-P µg l" t17 
		ON  (  ( t17.Site = t1.Site )  AND  ( t17.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend pH" t16 
		ON  (  ( t16.Site = t1.Site )  AND  ( t16.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend NO2-N µg l" t15 
		ON  (  ( t15.Site = t1.Site )  AND  ( t15.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend NO2+NO3-N µg l" t14 
		ON  (  ( t14.Site = t1.Site )  AND  ( t14.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend NH4-N µg l" t13 
		ON  (  ( t13.Site = t1.Site )  AND  ( t13.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Na mekv l" t12 
		ON  (  ( t12.Site = t1.Site )  AND  ( t12.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Mn µg l" t11 
		ON  (  ( t11.Site = t1.Site )  AND  ( t11.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Mg mekv l" t10 
		ON  (  ( t10.Site = t1.Site )  AND  ( t10.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Kond_25 mS m25" t9 
		ON  (  ( t9.Site = t1.Site )  AND  ( t9.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend K mekv l" t8 
		ON  (  ( t8.Site = t1.Site )  AND  ( t8.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Fe µg l" t7 
		ON  (  ( t7.Site = t1.Site )  AND  ( t7.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend F mg l" t6 
		ON  (  ( t6.Site = t1.Site )  AND  ( t6.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Cl mekv l" t5 
		ON  (  ( t5.Site = t1.Site )  AND  ( t5.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Ca mekv l" t4 
		ON  (  ( t4.Site = t1.Site )  AND  ( t4.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Alk. Acid mekv l" t3 
		ON  (  ( t3.Site = t1.Site )  AND  ( t3.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Abs._F 420nm 5cm" t2 
		ON  (  ( t1.Site = t2.Site )  AND  ( t1.Season = t2.Season ) )  ;
1 ACCEPTED SOLUTION

Accepted Solutions
gzmorgan0
Super User (Alumni)

Re: multiple join

Look up the JMP function Query()

 

Query(<<dt1|Table(dt1, alias1)>, ..., <dtN, aliasN)>>, <private | invisible>, <scalar>, sqlStatement )
Description
Performs a SQL query on selected data tables.
 
Of course, you can use JMP Query Builder in a ascript. When you ran the JMP Query Builder script and it ran, the resulting table should have a Run Query script attached. This is the JSL script to run the same query.
 
Hope that helps.

View solution in original post

1 REPLY 1
gzmorgan0
Super User (Alumni)

Re: multiple join

Look up the JMP function Query()

 

Query(<<dt1|Table(dt1, alias1)>, ..., <dtN, aliasN)>>, <private | invisible>, <scalar>, sqlStatement )
Description
Performs a SQL query on selected data tables.
 
Of course, you can use JMP Query Builder in a ascript. When you ran the JMP Query Builder script and it ran, the resulting table should have a Run Query script attached. This is the JSL script to run the same query.
 
Hope that helps.