query

hi everyone

i am trying to join three tables(2 same tables and one different table) and it is taking very long time

here is the query
can anyone optimize the query as it is taking very long time to run

i have 2 tables
tablea
tableb

select * from tablea a1 inner join (select * from tableb where condition) a3
on a1.col1=(select max(col1) from tablea a2
where a3.col2=a2.col2)

i am running this same query based on a condition with col2 and it is working fine and i was using only tables a1 and a3 once.

any suggestions welcome
thanks

Quote:

Originally Posted by LAKS

hi everyone

i am trying to join three tables(2 same tables and one different table) and it is taking very long time

here is the query
can anyone optimize the query as it is taking very long time to run

i have 2 tables
tablea
tableb

select * from tablea a1 inner join (select * from tableb where condition) a3
on a1.col1=(select max(col1) from tablea a2
where a3.col2=a2.col2)

i am running this same query based on a condition with col2 and it is working fine and i was using only tables a1 and a3 once.

any suggestions welcome
thanks

Can u explain your Requirement, then its easy to give sugges|||

Quote:

Originally Posted by LAKS

hi everyone

i am trying to join three tables(2 same tables and one different table) and it is taking very long time

here is the query
can anyone optimize the query as it is taking very long time to run

i have 2 tables
tablea
tableb

select * from tablea a1 inner join (select * from tableb where condition) a3
on a1.col1=(select max(col1) from tablea a2
where a3.col2=a2.col2)

i am running this same query based on a condition with col2 and it is working fine and i was using only tables a1 and a3 once.

any suggestions welcome
thanks

try

Select (all coulmsn from table a)(all columns from table b) from tablea a1 inner join tableb a2 on a1.col = a2.col
where a1.col in (select max(col1) from tablea a2 where a3.col = a2.col)

I was laso wondering if two of the tables are same and have the same data then in might be feasable just to use a1 instaed of a3 in the subquery|||This is probably what you want

select * from tablea a1 where a1.col1 = (
select max(col1) from tablea a2 inner join (select * from tableb where condition) a3 on a3.col2=a2.col2 )