Subselect Column Invalid issue

Question

I have a sql issue with column names in a subselect im guessing its because it has yet to be assigned that name but I cannot work out how to rearange it.

select Distinct Captains.Name, Captains.Team, (select count(Winners.Name) from (select HomeTeamCaptain As Name from fixture where fixture.HomeTeamCaptain = Captains.Name And fixture.matchResult = fixture.HomeTeam UNION ALL select AwayTeamCaptain As Name from fixture where fixture.AwayTeamCaptain = Captains.Name And fixture.matchResult = fixture.AwayTeam) As Winners) As Winners From (select fixture.HomeTeamCaptain As Name, HomeTeam As Team From fixture UNION ALL select fixture.AwayTeamCaptain As Name, AwayTeam As Team From fixture) As Captains order by Name;

The "Captains.Name" is the issue I need it to run the Count - Subselect but cannot get hold of its value!

Answer 1

This works for me:

select distinct Captains.Name, Captains.Team, 
    (select count(*)  
     from fixture 
     where 
        --I changed this a bit
        (fixture.HomeTeamCaptain = Captains.Name And
         fixture.matchResult = fixture.HomeTeam) 
     OR
        (fixture.AwayTeamCaptain = Captains.Name And 
         fixture.matchResult = fixture.AwayTeam)
    ) As Winners

FROM (select fixture.HomeTeamCaptain As Name, HomeTeam As Team 
     from fixture 
     UNION ALL 
     select fixture.AwayTeamCaptain As Name, AwayTeam As Team 
     from fixture) AS Captains
order by Captains.Name;

You should consider creating a Captains table and reference "Home" and "Away" captains by an Id.