Problem with Where Clause

[delassus]

I am fairly new to programming in general and have started out with PHP and MySQL. I am having a simple problem that I can't quite figure out and was hoping someone could help.

I am trying to obtain the record of a certain team in their first year of play and am trying to use the MIN(year) in the where clause and is doesn't work. Any suggestions?

Here is the select statement:

SELECT

SUM(wlt='W') AS win,
SUM(wlt='L') AS loss,
SUM(wlt='T') AS tie

from games

where team1 = '$team'
and year = MIN(year)

David

[mrmufin]

Try putting the MIN() expression in your SELECT statement:

"SELECT
SUM(wtt="W") AS "Win",
SUM(wtt="L") AS "Loss",
SUM(wtt="T") AS "Tie",
MIN(year)
FROM games WHERE team1 = '$team'"

...although I'm not really sure about your use of the SUM() function here.

[delassus]

putting the MIN(year) in the SELECT statement does not do anything. The following expression does work but I want to be able to write a statement to find the minimum year and not hard code it because it will change for different teams.

This works:

SELECT

SUM(wlt='W') AS win,
SUM(wlt='L') AS loss,
SUM(wlt='T') AS tie

from games

where team1 = '$team'
and year = 1892

Any other suggestions?

David

[Lars_Berg]

SELECT

SUM(wlt='W') AS win,
SUM(wlt='L') AS loss,
SUM(wlt='T') AS tie

from games

where team1 = '$team'
group by year
order by year limit 1

Normally this type of query would be done better with a subquery

SELECT

SUM(wlt='W') AS win,
SUM(wlt='L') AS loss,
SUM(wlt='T') AS tie

from games

where team1 = '$team'
and year in (select min(year) from games where team = '$team')

but this is not supported by Mysql.

[delassus]

Lars,

Thank you for the help. I was about to add a reply to get it to work I had to group them also, but I see you revised your post to state the same.

I also want to list the record for the last year played and all I need to do is order by DESC and I get what I want. Just have to get my mind thinking how to use the different functions.

David