mySQL subquery problem

[rmorgan]

HI, I am trying to join a bunch of queries together so that I can convert an
html table into a cfgrid, and having problems. The query is:
<cfquery name="getteams" datasource="#ds#">
SELECT teams.teamname
FROM teams
WHERE teams.age_id = '#session.ageid#'
(SELECT count(homescore, vistorscore) as wins
FROM schedule
WHERE homescore > visitorscore
AND visitorscore < homescore)
GROUP BY teams.team_id
</cfquery>
I know I probably have the format wrong but I have moved it everywhere and it
does not seem to matter the error being reported is:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '(SELECT
count(homescore, vistorscore) as wins from schedule where homescore ' at line 4


The error occurred in C:\Program Files\Apache
Group\Apache2\htdocs\LeagueTracking\results\result s.cfm: line 34

32 : SELECT teams.teamname
33 : FROM teams
34 : WHERE teams.age_id = '#session.ageid#'
35 : (SELECT count(homescore, vistorscore) as wins
36 : from schedule

Could someone assist me in getting going in the right direction? I have
several other queries to add to this, and I am sure I can get the rest if I get
going in the right direction. TIA.

[Mountain Lover]

well, assuming that you are running 4.1+ that supports subqueries...
you cannot specify 2 columns in a count(), however you can use count(*).
So, that being said, this should work:

<cfquery name="getteams" datasource="#ds#">
SELECT teams.teamname,
(SELECT count(homescore) FROM schedule WHERE homescore > visitorscore
AND visitorscore < homescore) as wins
FROM teams
WHERE teams.age_id = '#session.ageid#'
GROUP BY teams.team_id
</cfquery>

HTH
--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[Kronin555]

What version of MySQL is running on your server? Older versions don't support subqueries, at all.

[Dan Bracuk]

Definetely bad syntax. Also, this:
WHERE homescore > visitorscore
AND visitorscore < homescore

is redundant.

What's the relationship between your teams and schedule table?

Originally posted by: rmorgan
HI, I am trying to join a bunch of queries together so that I can convert an
html table into a cfgrid, and having problems. The query is:
<cfquery name="getteams" datasource="#ds#">
SELECT teams.teamname
FROM teams
WHERE teams.age_id = '#session.ageid#'
(SELECT count(homescore, vistorscore) as wins
FROM schedule
WHERE homescore > visitorscore
AND visitorscore < homescore)
GROUP BY teams.team_id
</cfquery>
I know I probably have the format wrong but I have moved it everywhere and it
does not seem to matter the error being reported is:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '(SELECT
count(homescore, vistorscore) as wins from schedule where homescore ' at line 4


The error occurred in C:\Program Files\Apache
Group\Apache2\htdocs\LeagueTracking\results\result s.cfm: line 34

32 : SELECT teams.teamname
33 : FROM teams
.34 : WHERE teams.age_id = '#session.ageid#'
35 : (SELECT count(homescore, vistorscore) as wins
36 : from schedule

Could someone assist me in getting going in the right direction? I have
several other queries to add to this, and I am sure I can get the rest if I get
going in the right direction. TIA

[Dan Bracuk]

Actually no, it won't work. First, you are selecting teamname and grouping by
teamid, so that will crash. Second, there is nothing in your query that
matches the team to the scores.

Originally posted by: Newsgroup User
well, assuming that you are running 4.1+ that supports subqueries...
you cannot specify 2 columns in a count(), however you can use count(*).
So, that being said, this should work:

<cfquery name="getteams" datasource="#ds#">
SELECT teams.teamname,
(SELECT count(homescore) FROM schedule WHERE homescore > visitorscore
AND visitorscore < homescore) as wins
FROM teams
WHERE teams.age_id = '#session.ageid#'
GROUP BY teams.team_id
</cfquery>

HTH
--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[mxstu]

>Actually no, it won't work. First, you are selecting teamname and grouping by
teamid,

>so that will crash.

Dan,

That is not strictly true. It depends on your database. This query is
allowed in mySQL (at least in 4.1)

SELECT teams.teamname
FROM teams
GROUP BY teams.team_id

[Mountain Lover]

oops, just pasted what was there, didn't really look at anything other
than the subquery, I don't know his db design. I presume he does!
--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[rmorgan]

Thank you guys, but I got it figured out. I turned 265 lines of code for html
presentataion to this:
select team as teamname, sum(wins) as wins, sum(loss) as loss, sum(tie) as
tie, sum(forfeit) as forfeit, sum(GP) as GP, sum(GF) as GF, sum(GA) as GA,
(sum(GF)-sum(GA)) as GD, (sum(GF) / sum(GP)) as avggf, (sum(GA) / sum(GP)) as
avgga, (sum(wins)*3 + sum(tie)*2 + sum(loss)*1) as Points
from (select home as team,
sum(if(homescore>visitorscore,1,0)) as wins,
sum(if(visitorscore>homescore,1,0)) as loss,
sum(if(homescore=visitorscore,1,0)) as tie,
sum(if(homeforfeit=1,1,0))as forfeit,
count(home) as GP,
sum(homescore)as GF,
sum(visitorscore) as GA
from schedule group by team
union
select visitor as team,
sum(if(homescore < visitorscore,1,0)) as wins,
sum(if(visitorscore < homescore,1,0)) as loss,
sum(if(homescore=visitorscore,1,0)) as tie,
sum(if(visitorforfeit=1,1,0))as forfeit,
count(visitor) as GP,
sum(visitorscore) as GF,
sum(homescore)as GA
from schedule group by team) as table1 group by team
its adding everything perfectly, I just needed a nudge. TXS GUYS