>>Todd,
>>Make second join and 'inner' join.
>>Also moving " and child.location = 'MAIN'" to where would work but having that in join sounds better to me.
>>Cetin
>
>moving the = 'MAIN' to the where clause fixed it. It still sounds better to have it in the join condition, but at this moment, it works, I'm gonna run with it. :-)
>
>Thanks for your help and all the hard work by Vlad!
Todd,
I missed you want 'NULL' if no match (as reading your example). Try this one :
select master.*, child.altid as myfield ;
from master ;
left outer join crossref on master.id = crossref.id ;
left join child on crossref.id2 = child.id2 ;
where master.id = 2 and iif(!isnull(crossref.id), child.location = 'MAIN', .t.)
Cetin