Neil,

< snipped >
>
>Sometimes. The problem is caused by schools that have no GVB coach. Some schools don't have any coach entries. The hcontact table is related to the HS table by the ceeb (school id). Each coach entry has a field for the sport that they coach. I am querying the sport info on the student, but the coach sport must match also. I don't want the FB coach for a GVB student.
>
>
< snipped >
>Based on my research, the left outer join should work. There must be some clause or clause order that is preventing it from working. I'm experimenting with scratch programs to see if I can figure it out.

The problem is you are specifying selection criteria based on a table that may or may not have values in the result set. Once you explicitly base the selection criteria on that table, all resulting records must adhere to it. Try adding a compound filter to the left outer join.
Ex.
left join hcontact on hs.ceeb+"GVB" = hcontact.ceeb+hcontact.sport ;

If ceeb is an integer then you can use the following to convert it to character:

chrtran(transform(hs.ceeb,"99999"),space(1),"0")+"GVB" = ;
   chrtran(transform(hcontact.ceeb,"99999"),space(1),"0")+hcontact.sport

This will make the query non-Rushmore optimizable but it may return the desired results.

>
>>
>>< snipped >
>>>This is a database of athletes and high schools with coaches. Tables in the query are Students, HS (schools) and Hcontact (coaches). Each school may have several coaches (FB, GBK, GVB, BBK, etc.) Some schools don't have an entry for the GVB coach, but we have students entered for that sport at the school.
>>>
>>>I want my query to select only the student-athletes with sport = GVB between certain years, and to include the coach name. I need the student-athlete even if there is no corresponding GVB coach entry. There may be other sport coaches (FB, BK) which I DO NOT want.
>>>
>>< snipped >
>>
>>>

>>>SELECT ;
>>>	students.student_id, ;  && (other student fields - deleted for brevity)
>>>	hs.hs_name, ;
>>>	hs.address1, ;
>>>	hs.address2, ;
>>>	hs.city as hs_city, ;
>>>	hs.state as hs_state, ;
>>>	hcontact.fname as coach_fname, ;
>>>	hcontact.lname as coach_lname,  ;
>>>	hcontact.home_phone as coach_hmphone, ;
>>>	hcontact.work_phone as coach_wkphone,;
>>>	students.pos_date, ;
>>>	Students.comments, ;
>>>	SPACE(160) AS notes ;
>>>	FROM  Students INNER JOIN f:\names\temp\gvbrpt;
>>>	on students.student_id = gvbrpt.student_id ;
>>>	inner join hs;
>>>	on gvbrpt.ceeb = hs.ceeb;
>>>	left join hcontact;
>>>	on hs.ceeb = hcontact.ceeb ;
>>>	WHERE YEAR BETWEEN "01" AND "04";
>>>        having hcontact.sport = 'GVB';  && I've tried this as part of the WHERE clause, too.
>>>	ORDER BY hs_state, hs_city, slname, sfname ;
>>>	INTO table f:\names\temp\gvb_all
>>>SELECT gvb_all
>>>REPLACE ALL notes WITH ALLTRIM(comments)

Larry Miller
MCSD
LWMiller3@verizon.net

Accumulate learning by study, understand what you learn by questioning.  -- Mingjiao