UNION QUERY - HOW TO DO IT?

[#1]

Dear All,

We are looking for your help. We are creating a query that extract the data from several tables (as usual), but we would like to do a Union between two primary tables. An example of how our SQL query looks like is below. How can we achieve this using the Query Builder? So far, we can query the db using only the first part of the query before the UNION statement. We have read the JSL books, but we could not find what we are looking for. Your help is highly appreciated!

So the original SQL query looks something like this:

USE BLAH -- this is the name of the DB

(SELECT
GENO.NAME AS [GENOTYPE],
GENO.PEDIGREE AS [PEDIGREE],
EXPMT.NAME AS [EXPERIMENT],
LOC.NAME AS [LOCATION],
TRAITS.NAME AS [TRAIT],
MEANS.YEAR AS [YEAR],
MEANS.AVG AS [DATA]

FROM BLAH.dbo.MEANS
INNER JOIN BLAH.dbo.GENO ON GENO.ID=MEANS.ENT1
INNER JOIN BLAH.dbo.EXPMT ON EXPMT.ID=MEANS.EXPT
INNER JOIN BLAH.dbo.LOC ON LOC.ID=EXPMT.LOC
INNER JOIN BLAH.dbo.TRAITS ON TRAITS.ID=MEANS.TRAIT

WHERE GENO.NAME IN ('a', 'b', 'c')

AND TRAITS.NAME = 'YIELD' 
AND LOC.NAME = 'FARGO'

AND MEANS.YEAR IN ('2015','2016','2017'))

UNION(

SELECT 
GENO.NAME AS [GENOTYPE],
GENO.PEDIGREE AS [PEDIGREE],
EXPMT.NAME AS [EXPERIMENT],
LOC.NAME AS [LOCATION],
TRAITS.NAME AS [TRAIT],
MEANS2.YEAR AS [YEAR], 
MEANS2.AVG AS [DATA]

FROM BLAH.dbo.MEANS2
INNER JOIN BLAH.dbo.GENO ON GENO.ID=MEANS2.ENT1
INNER JOIN BLAH.dbo.EXPMT ON EXPMT.ID=MEANS2.EXPT
INNER JOIN BLAH.dbo.LOC ON LOC.ID=EXPMT.LOC
INNER JOIN BLAH.dbo.TRAITS ON TRAITS.ID=MEANS2.TRAIT

WHERE GENO.NAME IN ('a', 'b', 'c')

AND TRAITS.NAME = 'ALPHA_AMYLASE' 
AND LOC.NAME = 'FARGO'

AND MEANS2.YEAR IN ('2015','2016','2017'))

ORDER BY TRAITS.TBFLD_NAME

[#2]

@anaheilman, 
             Let us assume you have a working SQL Query, if you do you should be able to use Open Database() to connect JMP to your database and pull the results of your SQL Query. 

File ==> Scripting Index ==> "Open Database"

[#3]

Hi Uday,
My query works in JMP, the first part before the union. My question is how to put together using the JMP language the Union query since we have two primary tables Means and Means2.

Thanks for your help. AM

[#4]

@anaheilman, 
        What is not clear to me is - when you say part of the query works, that doesn't really make sense. If you have 2 tables - "Means" and "Means2", you can use Join function to perform the equivalent of the union. If your SQL query already performs the union, then you should just be able to use Connect DataBase() 

[#5]

There are two options that I know of:

1. Run two queries and concatenate the two resulting tables using JSL.  dt1 << Concatenate(dt2);
2. Use the New SQL Query Custom Query.

Below is an example *. It is finds all instances where name matches in the 2 tables or where name is empty (null) in table #1 (t1).   This is not your scenario, since this is using JMP tables, but it demonstrates that you can use your SQL statement in a CustomSQL().

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Big Class Joins Table1" =>
		"C:\temp\JSLC2_working\scripts\Big Class Joins Table1.jmp",
		"Grade Level Joins Table2" =>
		"C:\temp\JSLC2_working\scripts\Grade Level Joins Table2.jmp"]
	),
	QueryName( "SQLQuery1" ),
	CustomSQL(
		"SELECT t1.name, t1.age, t2.name AS \!"name 2\!", t2.\!"grade level\!" 
FROM \!"Big Class Joins Table1\!"  t1 
	LEFT OUTER JOIN \!"Grade Level Joins Table2\!" t2 
		ON   ( t1.name = t2.name ) 
UNION ALL
SELECT t1.name, t1.age, t2.name AS \!"name 2\!", t2.\!"grade level\!" 
FROM \!"Grade Level Joins Table2\!" t2 
	LEFT OUTER JOIN \!"Big Class Joins Table1\!"  t1 
		ON   ( t2.name = t1.name ) 
WHERE t1.name IS NULL
"
	)
) << Run

I am sure this blog has many SQL experts (I am not an expert), that might provide a better solution.

*It is an early version of a script created for JSL Companion, 2nd Ed.  

[#6]

Thank you all for your help. I will follow your recommendations and let you know if I encounter any issues.

Have a great day! AM