Use the technique called "correlated subquery" to get the result you want. You can search online for more info about correlated subquery.

The query will look like:

Select A.a,A.b,A.c,B.d,B.e, D.f, D.g

from A,B, (

SELECT C.f,C.g from A,C where A.a = C.a  AND C.a

) as D

where A.z = B.a

I did not compile or execute the SQL, but your final SQL should look like mine.

Hope it helps.

------------------------------------------------

Tim Robson, MCSD, MCDBA

Comm100, Live Chat Software Provider