Joining Tables Inside the Query Function

David_G

Hi,

I am fairly knew to JSL scripting, although I have some custom SQL queries I wrote in query builder. I am trying to put these queries into JSL script without having to rewrite them using the JOIN function  

 

Is it possible for me to join the tables in the Query() function?

 

For example   dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

                       dr = Open( "$RANDOM_DATA/Small Class.jmp" );

 

Below is what I'm trying/ guessing the code should look like

RESULT = QUERY (dt, dr, "SELECT * 

                  FROM 'Big Class'  BC

                  JOIN 'Small Class' SC on SC.name = BC.name;");

 

The only examples I found were of the Query function being used with 1 table.

 

Thanks,

 

David

 

cwillden

The Query() function in the Scripting index actually has a nice example of what you want to:

// Using aliases, performing a join
dtSAT = Open(
	"$SAMPLE_DATA/SATByYear.jmp",
	Invisible
);
dtUS = Open(
	"$SAMPLE_DATA/US Demographics.jmp",
	Invisible
);
Query(
	Table( dtSAT, "t1" ),
	Table( dtUS, "t2" ), 

	"\[SELECT t1.State, t1."SAT Math", t2."College Degrees",
            t2."Eighth Grade Math"
       FROM t1
       LEFT OUTER JOIN t2
           ON t1.State = t2.State
       WHERE t1.'SAT Math' > 550;
      ]\"
);