CODEWITHSUNDEEP
javasqlmysql
Ashish Agarwal
Ashish Agarwal

Reputation: 6283

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

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

Table_2

I want to join these two result in the RESULTSET AS

Table_Resultant

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

Upvotes: 0

Views: 2662

Answers (2)

PatRedway
PatRedway

Reputation:

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 system, here's the outline of a solution, assuming that:

1. Fact1 and 2 are >=0 ints and 
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

Upvotes: 2

djna
djna

Reputation: 55897

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?

Upvotes: 0

Related Questions