SQL Noob
I decided to put off WOW last night to work on my NCL chess rating site. Shocking, I know. I am not much of a SQL expert, and I quickly came across a simple problem that I had no idea how to solve in SQL. I created a table to hold the Pairings/Results, and that has foreign keys back to the Member table - one key for white, one key for black. In order to do a display for any pairings and results I wanted to pull the names from the Member table using the keys, and that's when I realized I didn't know how to do it. How could I pull different names from the same table using foreign keys in another table, all with the same query?
The answer turned out to be pretty simple - you just join against the Member table twice in this case. It just never occurred to me that you could do that.
For the ultra-curious, here is the SQL:
String sql = "select s.id,s.tournamentId,s.sectionId,s.whiteId,s.blackId,s.result,s.round, w.name as whiteName, b.name as blackName from pairing s"+
"inner join member w on s.whiteId=w.id"+
"inner join member b on s.blackId=b.id "+
"where tournamentId="+s.getTournamentId()+
" and sectionId="+s.getId();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
s.getPairings().clear();
while (rs.next()) {
Pairing p = new Pairing();
p.setId(rs.getLong("id"));
p.setTournamentId(rs.getLong("tournamentId"));
p.setSectionId(rs.getLong("sectionId"));
p.setWhiteId(rs.getLong("whiteId"));
p.setBlackId(rs.getLong("blackId"));
p.setWhiteName(rs.getString("whiteName"));
p.setBlackName(rs.getString("blackName"));
s.getPairings().add(p);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
if (rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (stmt != null) stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (con != null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}