Hi all,
AAAAAARRGGHHH! I've been trying to get this query with a subquery to work but it seems I'm doing something wrong. All of the required info is below but in summary:
Players have one or more PlayerStatus's.
PlayerStatus's have a year.
I want all players, but only the team that's shown in the latest year for each player.
The SQL query I'm trying to replicate is:
Code:
select p.playerId, p.surname, p.firstname, ps.team
from player p, playerstatus ps
where p.playerid = ps.playerId
and ps.year = (select max(year) from playerStatus where playerId = p.playerId)
order by p.surname
The HQL equivilent I've tried is:
Code:
select player.id, player.surname, player.firstName, player.playerStatus.team
from Player player
where player.playerStatus.year = (
select max(ps.year)
from PlayerStatus ps
where ps.player = player
)
order by player.surname
Which produces:
Code:
select
player0_.playerId as col_0_0_,
player0_.surname as col_1_0_,
player0_.firstName as col_2_0_,
playerstat1_.team as col_3_0_
from
player player0_,
playerstatus playerstat1_,
playerstatus playerstat2_
where
player0_.playerId=playerstat2_.playerId
and player0_.playerId=playerstat1_.playerId
and playerstat2_.year=(
select
max(playerstat3_.year)
from
playerstatus playerstat3_
where
playerstat3_.playerId=player0_.playerId
)
order by
player0_.surname
The problem I've got is that the HQL query returns *all* player rows eg:
Code:
331 | Zalsman | Zeke | 10 Red
331 | Zalsman | Zeke | 10 Red
331 | Zalsman | Zeke | 10 Red
Instead of just the latest one.
What have I done wrong here? I've been tearing my hair out for hours now.
Thanks,
Greg
---------------------------------------------------------------------------
Hibernate version:
3.0.5
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="data.Player" lazy="false" table="player">
<id name="id" column="playerId" type="long">
<generator class="increment"/>
</id>
<property name="surname" type="string"/>
<property name="firstName" type="string"/>
<many-to-one name="address" lazy="false" column="addressId" class="data.Address" cascade="all" not-null="false"/>
<property name="dob" type="date"/>
<property name="phoneNumber" type="string" column="phoneNo"/>
<property name="firstEnrolled" type="string" column="firstEnrolment"/>
<many-to-one name="registrationStatus" column="registrationStatusId" lazy="false" class="data.RegistrationStatus" not-null="true"/>
<property name="birthCertificate" type="string"/>
<set name="parents" lazy="false" inverse="true">
<key column="playerId"/>
<one-to-many class="data.Parent"/>
</set>
<set name="playerStatus" lazy="false" inverse="true" sort="data.util.PlayerStatusComparator">
<key column="playerId"/>
<one-to-many class="data.PlayerStatus"/>
</set>
<set name="medicalHistory" lazy="false" inverse="true">
<key column="playerId"/>
<one-to-many class="data.Medical"/>
</set>
<set name="notes" lazy="false" inverse="true" sort="data.util.NoteComparator">
<key column="playerId"/>
<one-to-many class="data.Note"/>
</set>
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="data.PlayerStatus" lazy="false" table="playerstatus">
<id name="id" column="playerStatusId" type="long">
<generator class="assigned"/>
</id>
<many-to-one name="player" column="playerId" class="data.Player" not-null="true"/>
<property name="year" type="string"/>
<property name="paid" type="boolean"/>
<property name="amount" type="double"/>
<property name="playing" type="boolean"/>
<property name="team" type="string"/>
<property name="gamesForYear" type="int"/>
<property name="gamesToYear" type="int"/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
I'm using the hibernate tools hql console - here is the query:
select player.id, player.surname, player.firstName, player.playerStatus.team
from Player player
where player.playerStatus.year = (
select max(ps.year)
from PlayerStatus ps
where ps.player = player
)
order by player.surname
Name and version of the database you are using:
MySQL 5.0.10a-beta
The generated SQL (show_sql=true):
from the console tool:
select
player0_.playerId as col_0_0_,
player0_.surname as col_1_0_,
player0_.firstName as col_2_0_,
playerstat1_.team as col_3_0_
from
player player0_,
playerstatus playerstat1_,
playerstatus playerstat2_
where
player0_.playerId=playerstat2_.playerId
and player0_.playerId=playerstat1_.playerId
and playerstat2_.year=(
select
max(playerstat3_.year)
from
playerstatus playerstat3_
where
playerstat3_.playerId=player0_.playerId
)
order by
player0_.surname