I have the typical master, crossref, child set up. I'm trying to retrieve a particular child record, and having trouble.

My master record links to 4 crossref records, which each link to 1 child record. I need to pull the child record with the location field of "MAIN"

select master.*, child.altid as myfield ;
from master ;
left outer join crossref on master.id = crossref.id ;
left outer join child on crossref.id2 = child.id2 and child.location = 'MAIN' ;
where master.id = '1'

The problem is that this creates 4 records in the result set -- and I only expect 1. I've simplified the problem a bit -- I have a feeling that there is a nuance of SQL syntax I'm missing. This select is going to end up in a veiw that I am using for reporting. If all else fails, I'll make seperate views and link em together with set relation -- I know that works ;-)