SQL Join Question

[barry769]

I have a need to create a query from two systems that have the same data, but different terminology. I have a third table that equates the two system's terminology like follows.

System 1
OFFICE -- POSITION-- COUNT
a --------- director------------- 1
a --------- analyst ------------- 10
b --------- director------------- 1
b --------- logistics ----------- 3
b --------- engineer----------- 3
b --------- secretary -----------2
c --------- engineer------------2

System 2
OFFICE -- POSITION -- COUNT
a --------- Program Dir -------- 1
a --------- Prod Anal -------- 12
b --------- Division Dir -------- 1
b --------- Sr Log ----------- 1
c --------- Division Dir -------- 1
c --------- Comp Eng -------- 3



POSITIONS
SYS1 --- SYS2
director ------Program Dir
director ------Division Dir
analyst ------ Prod Anal
logistics ---------Sr Log
logistics ---------Jr Log
engineer-----Mech Eng
engineer-----Gen Eng
secretary -----Admin Assistant

What I need is to know how to get a table with
OFFICE - POS1-----POS2 ------ COUNT1 - COUNT2
a ------- director ------Program Dir ----1--------1
a ------- analyst ------Prod Anal---------10------12
b --------director ------Division Dir------1--------1
b --------logistics-----Sr Log ------------3---------1
b --------engineer--------------------------3---------0
b---------secretary--------------------------2--------0
c --------engineer-----Mech Eng ------2--------3

I think that I need something like the following, but I don't think that it is valid syntax.

SELECT system1.Office, system1.Position AS POS1, system2.Position AS POS2, system1.COUNT AS COUNT1, system2.COUNT AS COUNT2
FROM system1 
LEFT JOIN system2 ON 
(system1.Office = system2.Office) 
AND 
POS2 IN (SELECT sys2 FROM positions WHERE sys1 = POS1)