My usage of the sample code produced the same results as my own code, except there were missing master records :-(

Let me show some sample data, maybe we can get at it that way.

master.id, name
1, Todd
2, Vlad
3, Sergey
crossref.id, crossref.id2
1,5
1,6
1,7
child.id2, child.location, child.altid
5,BILL,A
6,MAIN,B
7,WORK,C

What I want in my result set is:
master.id, master.name, master.myfield
1, Todd, B

Does this make the problem clearer?

>Hi!
>
>

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

>
>Above return you correct data, when you do not expect situation when main record is required to return with no children. If you need such main record and NULL values in fields for child record if it does not exists, try following:
>
>

>select master.*, child.altid as myfield ;
>from (crossref ;
>inner join child on crossref.id2 = child.id2) ;
>right outer join master on master.id = crossref.id ;
>where master.id = '1' AND child.location = 'MAIN'
>

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