Java Servlet - matching 2 databases

Dear Experts,

I have 2 tables in a database, parents and memberparticulars resepectively.
parents is the table that contains parents data.
memberparticulars is the table that contains tutors data.

I'm trying to match parents' who has indicated subjects wanted, qualifications wanted from tutors to tutors who can teach that subjects and have the relevant qualifications.

My aim is to display the tutors' names, ID, contact number that fit the above criteria.

Hence, I created a separte servlet to do that.

But, I'm not sure if I need to join tables or not?

So, I use:

public class processMatch extends HttpServlet {

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException, ClassNotFoundException, SQLException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
Connection con = null;
ResultSet rs = null;
PreparedStatement ps = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/tangara_member", "tangara_karen", "csw9l?xPJpXA");

String query = "SELECT FROM memberparticulars, parents WHERE memberparticulars.strSubjectsME = parents.strSubjects" && memberparticulars = "Degree holder";

ResultSet resultSet = stmt.executeQuery(query);
response.setContentType("text/xml");
out.write("<html><body><H3>Display list of tutors which match Parents' subjects and levels requested</H3>");
out.write("<tr><td>Name</td></tr>");
out.write("<tr><td>Contact</td></tr>");
out.write("<tr><td>Subjects</td></tr>");
int i = 0;
while (resultSet.next()) {
i++;
out.write(resultSet.getString("strFullNameME"));
out.write(resultSet.getString("strContactME"));
out.write(resultSet.getString("strSubjectsME" + " "));
}
out.write("</body></html>");
con.close();
} catch (ClassNotFoundException cnfe) {
System.err.println("Error loading driver: " + cnfe);
} catch (SQLException sqle) {
System.err.println("Error with connection: " + sqle);
} finally {
out.close();
}}}

Please advise my query is correct or not?

Many thanks.

View Answers

June 7, 2010 at 11:55 AM

Hi Friend,

Use the following query:

"SELECT * FROM parents,memberparticulars WHERE parents.subjects = memberparticulars.subjects and parents.qualification = memberparticulars.degree";

In the table 'parents', we have taken id, subjects and qualification as fields and in 'memberparticulars', we have taken id, name, contact, subjects, degree as fields.

Hope that it will be helpful for you.
Thanks

July 21, 2010 at 10:51 AM

Dear experts,

So happy I get through this ask a question page again. Thank God.

I want to say "A BIG THANK YOU" for helping me about the matching codes.

It is working now after fine tuning the SQL query :-) but I can't do it without you showing me the steps.

My question is that the table display the 100% match.
[parents request = UPMATH LPMATH, tutor = UPMATH LPMATH]

What if I want to have 80% match also included, how do I do it?

If the parents ask for subjects to be tutored are UPMATH LPMATH and the tutors can teach UPMATH LPMATH LPENG.

I would like to include that tutor as well.

How do I change my query in order for that tutor's particulars to be displayed as well?

Looking forward to your guidance. Many thanks.