Hi, Larry;

Thanks for the thoughts. Replies are imbedded below...

>Do records exist in the hcontact table if there is no coach; that is, is this a 2-fold table holding data about available sports and its coach? If so then this should work. If it is only holding coach information then the WHERE/HAVING clause is messing you up because there is no coach record in your case. Is there another place you could query the sport information? Then you can use it in your WHERE/HAVING clause and the coach information would be drawn in on the OUTER JOIN.

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.


>As a design suggestion, could you add another table of Sports that has both students and coaches as children? This is merely a suggestion and I don't know the extent of your database so it may not be prudent.


The total database is large, and my first reaction is that it is not practical. Second reaction is that I will run into the same problem - If there is no coach entered, it won't pick up the student.


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.

Thanks,
Neil




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