CODEWITHSUNDEEP
sqlmysqlselectsubquery
Steve
Steve

Reputation: 3080

Subselect Column Invalid issue

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!

Upvotes: 0

Views: 212

Answers (1)

Alex Bagnolini
Alex Bagnolini

Reputation: 22382

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.

Upvotes: 2

Related Questions