SQL Query to Show When Golfer Not Attached to an Event/Year

I am working on a school assignment that has downright stumped me for days. The task is to, using a view (VAvailableGolfers), populate a list box with Golfers who are not tied to a given event/year selected from a combo box. Here is the data in the tables:

The expected output on the form, then, would be:

• 2015 shows Goldstein available
• 2016 shows no one available
• 2017 shows both Goldstein and Everett available

so, in other words, where there isn’t a record in TGolferEventYears for a golfer for a particular year

I have tried left joins, full outer joins, exists, not in, not exists, etc and I cannot seem to nail down the SQL to make it happen.

Here is the VB Form and the SQL backing it. I cannot figure out what to code in the view:

“SELECT intGolferID, strLastName FROM vAvailableGolfers WHERE intEventYearID = ” & cboEvents.SelectedValue.ToString

Here is the view, which I know isn’t giving correct output:

select tg.intGolferID, strLastName, intEventYearID
from TGolferEventYears TGEY, TGolfers TG
Where tgey.intGolferID = tg.intGolferID 
and intEventYearID not IN
         (select intEventYearID
         from TEventYears
         where intEventYearID not in
               (select intEventYearID
               from TGolferEventYears))

Appreciate any help

Answer

I usually approach this type of question by using a cross join to generate all possibly combination and then a left join/where to filter out the ones that already exist:

select g.intGolferID, g.strLastName, ey.intEventYearID
from TEventYears ey cross join
     TGolfers g left join
     TGolferEventYears gey
     on gey.intGolferID = g.intGolferID and
        gey.intEventYearID = ey.intEventYearID
where gey.intGolferID is null;