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:


The HQL equivilent I've tried is:


Which produces:


The problem I've got is that the HQL query returns *all* player rows eg:


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

try hql like your base sql
(playerStatus is set and you use it like property)

Nearly - basic SQL mistake - thanks for your help.

Instead of max(ps.year) i needed max(year). That did it. For the future, here's the full query:

yes, my mistake, i think max(ps1.year)