Vas87thRD Vas87thRD - 6 months ago 15
SQL Question

HSQLDB Subquery - Java

In Microsoft Access I have three queries that work -

qryAwayMatches:

SELECT MatchTeam.FootballMatchID, MatchTeam.TeamID, MatchTeam.GameType, MatchProtocol.MatchTeamID, MatchProtocol.GoalNumber, MatchProtocol.YellowCardNumber, MatchProtocol.RedCardNumber
FROM MatchTeam LEFT JOIN MatchProtocol ON MatchTeam.ID = MatchProtocol.MatchTeamID
WHERE (((MatchTeam.GameType)="Away"));


qryHomeMatches:

SELECT MatchTeam.FootballMatchID, MatchTeam.TeamID, MatchTeam.GameType, MatchProtocol.MatchTeamID, MatchProtocol.GoalNumber, MatchProtocol.YellowCardNumber, MatchProtocol.RedCardNumber
FROM MatchTeam LEFT JOIN MatchProtocol ON MatchTeam.ID = MatchProtocol.MatchTeamID
WHERE (((MatchTeam.GameType)="Home"));


qryMatchResult:

SELECT qryHomeMatches.FootballMatchID, qryHomeMatches.TeamID AS HomeTeamID, qryAwayMatches.TeamID AS AwayTeamID, qryHomeMatches.GoalNumber AS HomeTeamGoals, qryAwayMatches.GoalNumber AS AwayTeamGoals, [HomeTeamGoals]>[AwayTeamGoals] AS HomeTeamWin, [HomeTeamGoals]=[AwayTeamGoals] AS NoWin, [HomeTeamGoals]<[AwayTeamGoals] AS AwayTeamWin
FROM qryHomeMatches INNER JOIN qryAwayMatches ON qryHomeMatches.FootballMatchID = qryAwayMatches.FootballMatchID;


In my Java program, I show the result of the first two queries on a button press in the following way:

btnqryAwayMatches.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
String s;
JFrame frame;
String url = "jdbc:hsqldb:file:db_data/myFootballDB;ifexists=true;shutdown=true";
String username = "SA";
String password = "";

try {
con = DriverManager.getConnection(url, username, password);
st = con.createStatement();
s = "SELECT MATCHTEAM.FOOTBALLMATCHID, MATCHTEAM.TEAMID, MATCHTEAM.GAMETYPE, "
+ "MATCHPROTOCOL.MATCHTEAMID, MATCHPROTOCOL.GOALNUMBER, "
+ "MATCHPROTOCOL.YELLOWCARDNUMBER, "
+ "MATCHPROTOCOL.REDCARDNUMBER "
+ "FROM MATCHTEAM LEFT JOIN MATCHPROTOCOL ON MATCHTEAM.ID = MATCHPROTOCOL.MATCHTEAMID "
+ "WHERE (((MATCHTEAM.GAMETYPE)='Away'));";
rs = st.executeQuery(s);
ResultSetMetaData rsmt = rs.getMetaData();
int c = rsmt.getColumnCount();
Vector<String> column = new Vector<String>(c);
for (int i = 1; i <= c; i++) {
column.add(rsmt.getColumnName(i));
}
Vector<Vector<String>> data = new Vector<Vector<String>>();
Vector<String> row = new Vector<String>();
while (rs.next()) {
row = new Vector<String>(c);
for(int i = 1; i <= c; i++) {
row.add(rs.getString(i));
}
data.add(row);
}

frame = new JFrame();
frame.setTitle("Away Match Results");
frame.setSize(700,420);
frame.setLocationByPlatform(true);
frame.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
JPanel panel = new JPanel();
JTable table = new JTable(data, column);
JScrollPane jsp = new JScrollPane(table);
panel.setLayout(new BorderLayout());
panel.add(jsp, BorderLayout.CENTER);
frame.setContentPane(panel);
frame.setVisible(true);

} catch(Exception exc) {
exc.printStackTrace();
JOptionPane.showMessageDialog(null, "Error");
} finally {
try {
st.close();
rs.close();
con.close();
} catch(Exception exception) {
JOptionPane.showMessageDialog(null, "Error close");
}
}
}
});


How can I code the third query to execute on a button click? I understand that it needs to know the result of the first two queries to execute, so I am not sure to how to program this.

Answer

You can do it easily with the WITH clause:

WITH qryAwayMatches AS (SELECT MatchTeam.FootballMatchID, ...),
     qryHomeMatches AS (SELECT MatchTeam.FootballMatchID, ...)
     SELECT qryHomeMatches.FootballMatchID, ...

Put the full text of each of the queries in the template above.

Comments