Select unique records based on two criteria

[Lawdawg]

I'm searching a baseball database stat database, trying to determine who are the rookies. The database contains a record for each year a player has been in the league. So a player in the league for five years has five records, each of the five records has the same Player_ID but a different SEASON number. I'm trying retrieve the Player_IDs for all the rookies - which are those players with only a record for the current season and no other season records. Therefore, I want to retrieve those Player_IDs that have only a single season record. Any idea how this can be done efficiently?

I'm trying to avoid doing a database query for each player_ID and then determining if there was more than one season record returned - that seems awfully inefficient. Is there a way to do it in one query.

Thanks
Dan

[LordShryku]

The HAVING clause is what you need...

  SELECT player_id, count(season)
    FROM table
GROUP BY player_id
  HAVING count(season) = 1;

[rachel2004]

Dan, give this a try:

<?php 
$query = "SELECT COUNT(*) AS repetitions,
                             player_id
          FROM baseball_table
          GROUP BY player_id
          HAVING repetitions = 1";
?>

[rachel2004]

You're fast, LordShryku. 🙂

[LordShryku]

Don't tell the ladies 😃

[rachel2004]

hehe!

[Lawdawg]

I won't be able to try it till tomorrow morning, but thanks for the quick responses!

Dan