Reference tables from multiple ODBC sources to restrict records on load

[#1]

I have three tables that i would like to load, however because of their size, i am going to filter the records prior to load; an issue arises because the third table is on a different server; wondering how to reference table 1 in a where exists clause for table 3.

the structures are similar to below, and more detail are posted further down. 

table 1

Email	CustID	Country
email1@noemail.com	123	US
email2@noemail.com	456	US
email3@noemail.com	789	US
email4@noemail.com	333	CA
email5@noemail.com	888	MX

CustID	Age
123	23
456	45
789	60
333	54
888	19

table 3

CustID	OrderTotal
123	55.12
456	100.45
789	62.18
333	77.79
888	12.12

i planned to filter only for US

so i wrote the following:

ODBC CONNECT TO [SERVERNAME XXXXXX1] (XUserId isXXXXX, XPassword is XXXX);

SQL SELECT

    EMAIL,

   CUSTID,

  COUNTRY

FROM "SERVER"."DBNAME"."TABLE1" WHERE (COUNTRY = 'US');

--Filter table2 to include only those records that exist in table 1

SQL SELECT

    CUSTID,

   AGE,

FROM "SERVER"."DBNAME"."TABLE2" WHERE CUSTID in (SELECT CUSTID FROM "SERVER"."DBNAME"."TABLE1" WHERE (COUNTRY = 'US');)

Table3 however is on another server; is there a way to reference table1?

I would need to a new connection string. Wondering if i can name the table1 with an alias and reference it like:

ODBC CONNECT TO [SERVERNAME XXXXXX2] (XUserId isXXXXX, XPassword is XXXX);

SQL SELECT

    CUSTID,

   ORDERTOTAL,

FROM "SERVER2"."DBNAME"."TABLE3" WHERE CUSTID in (SELECT CUSTID FROM ALIAS_FOR_TABLE_1)

[Sokkorn · Accepted solution]

Hi James,

You may try this load script

ODBC CONNECT TO [SERVERNAME XXXXXX1] (XUserId isXXXXX, XPassword is XXXX);
[Table1]:
SQL SELECT
    EMAIL,
   CUSTID,
  COUNTRY
FROM "SERVER"."DBNAME"."TABLE1" WHERE (COUNTRY = 'US');


[CustID]:
LOAD Concat(CustID,',') AS CustIDVal Resident [Table1];
Let vCondition = Peek('CustIDVal',0,'CustID');
DROP Table [CustosID];


[Table2]:
SQL SELECT
    CUSTID,
   AGE,
FROM "SERVER"."DBNAME"."TABLE2" WHERE CUSTID in ($(vCondition));


ODBC CONNECT TO [SERVERNAME XXXXXX2] (XUserId isXXXXX, XPassword is XXXX);
SQL SELECT
    CUSTID,
   ORDERTOTAL,
FROM "SERVER2"."DBNAME"."TABLE3" WHERE CUSTID in ($(vCondition));

Hope this help.

Regards,

Sokkorn