java JDBC query question?

[nick2price2003]

I am trying to return the 3 fastest times from one of my database tables, along with the associated first name, last name and country of the time setters. All the fields are being returned to me, the only problem is that it returns every record in my database, not the fastest 3. UpdateEventSetGet is an object made up of the four variables.
any help appreciated
public List<UpdateEventSetGet> getFastest(String eveType, String roundType)
{

String roundType2 = roundType;
String eveType2 = eveType;

String SELECT_TOP_TIMES =
"select TOP 3 r.result, c.Nationality, c.First_Name, c.Last_Name " +
"from tblResults as r, tblCompetitor as c " +
"where r.Event_ID = " +
"(select e.ID " +
"from tblEvent as e " +
"where e.Event_Name = ?) " +
"and r.Round_ID = " +
"(select ro.ID " +
"from tblRound as ro " +
"where ro.Round_Number = ?) " +
"order by r.result";


PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<UpdateEventSetGet> topTimes = new ArrayList<UpdateEventSetGet>();

try
{
con = DatabaseUtils.connect(DRIVER, URL);
ps = con.prepareStatement(SELECT_TOP_TIMES);

for(int i = 0; i <= 2; i++)
{
ps.setString(1, eveType2);
ps.setString(2, roundType2);
}
rs = ps.executeQuery();

int i = 0;
while (rs.next())
{
String nationality = rs.getString(1);
String fName = rs.getString(2);
String lName = rs.getString(3);
String result = rs.getString(4);
UpdateEventSetGet update = new UpdateEventSetGet(nationality, fName, lName, result);
topTimes.add(update);

}


}
catch(Exception e)
{
System.out.println(e);
DatabaseUtils.rollback(con);
e.printStackTrace();
}
finally
{
DatabaseUtils.close(rs);
DatabaseUtils.close(ps);
DatabaseUtils.close(con);
}
return topTimes;


}

[KegC]

I don't know why you're getting more than 3 rows, but one problem is that you're not restricting the tblCompetitor table.

This means that even though you've restricted the tblResult table you'll still get duplicate rows for each competitor.


You need to add something along the lines of

r.competitor_id = c.competitor_id

This will make sure that only the competitor relating to a particular result are shown.