how to join or Union result of 2 sql queries in single result set

Question

i have 2 tables(result of two separate SQL queries and this result will be contained by List<Object> where each object represents 1 row of the database )

Table_1

• Dimension_1
• Dimension_2
• Fact_1

Table_2

• Dimension_1
• Dimension_2
• Fact_2

I want to join these two result in the RESULTSET AS

Table_Resultant

• Dimension_1
• Dimension_2
• Fact_1
• Fact_2

Due to some complication in querying part in my system i can't issue single query for the resultant table, and due to these limitations i will have have to join the two results List <object>(table_1 & table_2) into table_resultant every time

what are the possible approach for the Problem?

Is creating a Temporary table(to join the two resultsets) in MYSQL can be a potential solution??? Yes I am allowed to create temporary tables in Mysql

YES,"COMPLEX" SQL is permitted on the 2 "Results" to GET the resultant_table

Answer 1

Difficult to answer because you haven't really explained in detail what you can and can't do. At first sight you are trying to produce some data that would be the result of a simple JOIN - but you say you can't do that? If you are not permitted to do such a fundamental SQL thing it's hard to imagine what you can do. we'll need to know the nature of the limitations.

Can you use a stored procedure?

Answer 2

Hi, So you can do Union but not Join ? Could you clarify if you need to match values of Dimension1 and Dimension2 in the two source tables ? Or if you just need to take all values from both tables and merge them in the resultant one (in which case yes an Union might do the trick)

You need a full outer join, assuming that's not possible in your query syste, here's the outline of a solution, assuming

1. Fact1 and 2 are >=0 ints

2. no row in tablex where factx is null

   Select dimension1, dimension2, max(Fact1), max(Fact2) From ( Select dimension1, dimension2, Fact1, -1 as Fact2 UNION Select dimension1, dimension2, -1 , Fact2 ) GROUP by dimension1, dimension2

That's not the final solution, it will not return NULL for fact1 or 2 when the join doesn't match. For that of course you'd have to use CASE, or maybe if your querying system thinks that max(NULL,1)=1

Full solution with Union:
Select dimension1, dimension2,
CASE WHEN max(Fact1) = -1 THEN NULL ELSE Fact1 END,
CASE WHEN max(Fact2) = -1 THEN NULL ELSE Fact2 END,
From (
Select dimension1, dimension2, Fact1, -1 as Fact2
UNION Select dimension1, dimension2, -1 , Fact2 )
GROUP by dimension1, dimension2

If you can do a FULL OUTER JOIN on the two resultsets then it will look like this " Select
isnull(t1.dimension1, t2.dimension1),
isnull(t1.dimension2,t2.dimension2),
t1.fact1,
t2.fact2
FROM table1 t1 OUTER JOIN table2 t2
ON t1.dimension1=t2.dimension1 and t1.dimension2=t2.dimension2