I need advice on getting a query to work for a selected subset of the parent table when a matching child record does not exist. It seems like it should be an outer join, but doesn't work that way.

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.

All attempts I have made so far fail to include the athlete if there is no coach entry for that sport (Inner join behavior). Do outer joins not work in filtered queries using the WHERE clause?

How can I be sure to keep the student listing in this situation?

The code I currently have for the query is below.

Any ideas would be appreciated.
Thank you.

Neil Preston

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)