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;

[maxgro]

tblFiltered:

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

inner join (tblFiltered)
SQL SELECT * FROM Table2 as A;

Qlik will inner join tblFiltered (Table1 in SQL) and the second table (Table2 in SQL) using the field(s) with same name,  Account_Index

or you want 2 tables?

[#3]

Thanks, - the query seems to take longer than it should seems to be retrieving everything in Table2 for each item in tblFiltered. How do I retrieve only the values from Table2 that have an Account_Index value found in tblFiltered? When I write:

SQL SELECT * from Table2 as A WHERE A.Account_Index = tblFiltered.Account_Index;

I get an error for trying to use 'tblFiltered'.

My basic problem is I don't understand when and how I can use that table name of tblFiltered.

Also - yes, how would you script it for separate tables? I have a Table3, and a Table4 that I also want only to bring in rows where a matching Account_Index value appears in my tblFiltered version of Table1.

[#4]

Also - I'm trying another method, but I get an 'ODC Read Failed' error..

SQL SELECT * FROM (SELECT Account_Index FROM Prosurv.dbo.R_Acct WHERE Firm_Key='RJFS' AND Unit_Key='ACX') as A,
Prosurv.dbo.Account_Holder as B WHERE A.Account_Index = B.Account_Index;

[#5]

table1:

SQL SELECT *

FROM Table1

WHERE Firm_Key='myFilterValue';

table2:
SQL SELECT *

FROM Table2

where exists(Account_Index);

[#6]

This gives a syntax error.
Also - All of Table2 has the field 'Account_Index', but I just want results whose 'Account_Index' value matches to an item in Table1.

[#7]

Hi,

tblFiltered:

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

inner join(tblFiltered)

SQL SELECT * FROM Table2;

[maxgro · Accepted solution]

maybe

table1:

SQL

SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';

table2:

load *

where exists(Account_Index);

SQL

SELECT * FROM Table2;

[#9]

Thanks - that was suggested earlier, but there are too many results. My tblFiltered only has 50 results, and the other table is returning thousands, but I only want the 50 that also appear in tblFiltered. Their common field is 'Account_Index'. This works superfast and easy in SQL Studio. I don't get what the problem is here.

[#10]

edit:

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.