Openquery Join

Good morning,
I'm using Openquery to retrive data from DB2 z/OS Mainframe via MS SQL 2005 ENT linked server. I'm using MS Access as front end app to search for a record and display its values and then fill in earnings tax return. I have been able to retrive records just fine but I'm bit stumped when it comes to join in Openquery. I want to be able to add City/State from ZIPX_CODE table based on MSTR_ZIPC value from ETAX_MASTER table.
Here is SQL for getting specific values from ETAX_MASTER table;
SELECT MSTR_FIDN, MSTR_NAM1, MSTR_NAM2, MSTR_STRT, MSTR_ZIPC, MSTR_ATYP, MSTR_ADAT, MSTR_AEND FROM OPENQUERY(TSTESA5,'SELECT * FROM stl01.ETAX_MASTER where MSTR_FIDN =''26202479000''')
And here is the retrival of City and State just using zip code 63135;
SELECT ZIPX_CODE, ZIPX_CITY_ST FROM OPENQUERY(TSTESA5,'SELECT * FROM STL01.ETAX_ZIPX WHERE ZIPX_CODE = 63135')

Now I want to join these two and use MSTR_ZIPC code from ETAX_MASTER table and ZIPX_CODE and retrieve ZIPX_CITY_ST.

Thanks a lot


Amir

This statement actually works but its pulling whole ETAX_ZIPX table down and then joining causing 40 second delay.
select * from
OPENQUERY(TSTESA5,'SELECT MSTR_FIDN, MSTR_NAM1, MSTR_NAM2, MSTR_STRT, MSTR_ZIPC, MSTR_ATYP, MSTR_ADAT, MSTR_AEND FROM stl01.ETAX_MASTER where MSTR_FIDN =''26200206800''')as a inner join
OPENQUERY(TSTESA5,'SELECT ZIPX_CODE, ZIPX_CITY_ST FROM STL01.ETAX_ZIPX')AS p ON a.MSTR_ZIPC=p.ZIPX_CODE


Somehow this needs to be called as one big OPENQUERY statement...

Thanks

Instead of using two openqueries and joining it in SQL Server you should use join within one openquery.

I agree, that was exactly my issue. I didn't know how to get it all under one OPENQUERY umbrella so that it would take no time to run and all the work would be done by Mainframe.

In any case I was able to get one of the DB2 experts to help me with this and query is now running fine. Here is the correct language;
strsql = "SELECT MSTR_FIDN, MSTR_NAM1, MSTR_NAM2, MSTR_STRT, MSTR_XTRA, MSTR_ZIPC, MSTR_ATYP, MSTR_ADAT, MSTR_AEND, MSTR_WQTR, MSTR_WEND, MSTR_PDAT, MSTR_PEND, ZIPX_CITY_ST FROM OPENQUERY(TSTESA5,'SELECT STL01.ETAX_MASTER.*, STL01.ETAX_ZIPX.* FROM STL01.ETAX_MASTER, STL01.ETAX_ZIPX WHERE MSTR_FIDN ='" & "'" & Me!MSTR_FIDN & "'' AND MSTR_ZIPC = ZIPX_CODE')"