Code: Select all
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)
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;
Bascially it errors on saying that "Captains.Name" in the where clause of my subselect is not a valid column.
The idea of the query is based around Cricket and stats. It needs to get All captains that have played a match (be it a home or away team captain). Which it does with the outer query and select distinct. Then I do a Count on where the count is a subselect for finding if that captain was on the winning side (and there for a winning captain) Because I am union both the hometeam and awayteam captain wins it means I have a double subselect.
Code: Select all
select Distinct Captains.Name, Captains.Team,
(select count(fixture.HomeTeamCaptain) from fixture where fixture.HomeTeamCaptain = Captains.Name And matchResult = Captains.Team) As HomeWins,
(select count(fixture.AwayTeamCaptain) from fixture where fixture.AwayTeamCaptain = Captains.Name And matchResult = Captains.Team) As AwayWins
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;
This is another query for the same thing. Which does two separate counts one for home one for away teamcaptain wins. This works find (infact If I could just add the two columns up it would be acceptable. As you can see this also uses Captains.Name but I'm guessing because its only in one subselect that it allows it...