UNION subselect SQL issue

For help and advice about coding

Moderator: Tech Moderators

elmo
Posts: 56
Joined: Mon Jul 02, 2007 5:21 pm

UNION subselect SQL issue

Postby elmo » Mon Feb 22, 2010 1:11 pm

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

User avatar
Steve
Posts: 5392
Joined: Sun Mar 18, 2007 5:49 pm
Tag: SSSSSSSSssssssssss
Contact:

Re: UNION subselect SQL issue

Postby Steve » Mon Feb 22, 2010 6:43 pm

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.

User avatar
nomis
Site Admin
Posts: 1412
Joined: Sun Mar 18, 2007 4:50 pm
Tag: Hasn't realised that there are custom ranks yet
Contact:

Re: UNION subselect SQL issue

Postby nomis » Mon Feb 22, 2010 10:38 pm

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

User avatar
DJ
Posts: 2329
Joined: Sun Mar 18, 2007 8:48 pm
Tag: Cured
Contact:

Re: UNION subselect SQL issue

Postby DJ » Wed Jun 09, 2010 11:40 pm

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


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 3 guests

cron