how to use temp table in sql queries

[#1]

Hello,

I want to limit the results from one table on the 'Firm_Key' value. I also want to use the same limitation on several other tables, but which do not have the column 'Firm_Key'. The tables do share an 'Account_Index' column. All my attempts give errors. Thank you for your help.

tblFiltered:

SQL SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';

SQL SELECT * FROM Table2 as A
INNER JOIN tblFiltered as B ON A.Account_Index = B.Account_Index;

[#2]

Sorry forgot I can't use the where exists directly on a SQL select:

table1:

SQL SELECT *

FROM Table1

WHERE Firm_Key='myFilterValue';

table2:

load

*

where exists(Account_Index);

SQL SELECT *

FROM Table2 ;

Where exists first looks to see if a field has been prevouly loaded called Account_Index (which in this case has been in table one), it then looks to see if the Account_Index field exists in the new table and only brings back values where they match to table 1.

[jagan]

Hi,

Try like this

tblFiltered:

SQL SELECT * FROM Table1 Tab1

INNER JOIN Table2  Tab2 ON Tab2.KeyField = Tab.KeyField AND Firm_Key='myFilterValue'

INNER JOIN tblFiltered as B ON A.Account_Index = Tab2.Account_Index;

In select specify field names using in Dashboard.

Even it is slow, create indexes on the key fields.

Replace keyfield with your key fields.

Hope this helps you.

Regards,

Jagan

[#4]

Ok, I was wrong above, yes this does work. I don't know why it takes 20 minutes to run, though, when SQL Server processes the same query almost instantly. Thank you.