UNION subselect SQL issue

[elmo]

Hey Guys having a bit of a issue with a sql command I have.

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...

[Steve]

Are you positive that the results of each select statement are identical? (the number of columns and their domains must be the same)

IIRC they have to be the same.

[nomis]

Make sure each individual query that makes up the UNION works and returns what you would expect, and then put them back into a UNION.

What platform/db server is this for?

Maybe it's just me, but I don't like using temporary tables in the FROM clause.

Wouldn't this much simpler query return what you want?

Code: Select all

(SELECT HomeTeamCaptain AS Name, HomeTeam AS Team FROM fixture WHERE matchResult = Team)

UNION

(SELECT AwayTeamCaptain AS Name, AwayTeam AS Team FROM fixture WHERE matchResult = Team)

GROUP BY Name

[DJ]

Some "thank-you"s are missing from this corner of the forum I see, and it's not the only thread.