>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 ;-)
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