tags:

views:

320

answers:

2

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

A: 

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?

djna
YES I AM ALLOWED TO CREATE TEMPORARY TABLES using MYSQL
Sam Rudolph
Please don't use upper case like that, it looks like you are shouting. My question is not whether you can use temporary tables. It is exactly what limitations you have. To use temporary tables you would need SQL at least as complex as a JOIN. You say you can't use a JOIN, but we don't know what you can do.
djna
ok i will keep this thing for future,only limitation is i cant issue a single query(Like JOIN for retrieving the 2 RESULT) for the resultant table(Which is very obvious solution ) other than that i can use any thing on the two result Like Join or any thing else to get the resultant table******IMP TO NOTE******i cant apply JOIN while retrieving the 2 result but can GO for complex query OR JOIN on these two results....
Sam Rudolph
+1  A: 

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

Hello PatRedway,yes I have to match Dimension1 and Dimension2 from both the tables and retrieve the resultant table if fact1 exist and fact2 doesnot exist for a CASE than in that case row will look like dim1||dim2||Fact1||null
Sam Rudolph
Actually what you need then is a full outer join. If that's not possible then here's an inspiration for your solution assuming1-Fact1 and 2 are >=0 ints2-no row in tablex where factx is nullSelect dimension1, dimension2, max(Fact1), max(Fact2)From( Select dimension1, dimension2, Fact1, -1 as Fact2 UNION Select dimension1, dimension2, -1 , Fact2)group by dimension1, dimension2That'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
Sorry for the bad presentation of the code i'm new to stackoverflow :(
I've tried to copy into the answer. (It's sometimes better to update your original answer rather than add comments.)
djna
The mystery to me is whether any complex SQL is permitted my the query system in use.
djna
Thanx for the solution seems it will work but can you suggest me better way to handle the NULL value "case"
Sam Rudolph
@djna YES,"COMPLEX" SQL is permitted on the 2 "Results" to GET the **resultant_table**
Sam Rudolph
@PatRedway Yes I am ok with the assumptions
Sam Rudolph
@ashish : I put the full version of the query using UNION. However if you can work on the two resultsets I'd recommend using the outer join.
@PatRedway Thanx for the SOLUTION it WORKED..!!...KEEP HELPING..:)
Sam Rudolph
@ashish : glad I could help, good luck with your project :)
@PatRedway thnax for the LUCK!!,wish you same
Sam Rudolph