CODEWITHSUNDEEP
javadatabasesubqueryderby
Troller
Troller

Reputation: 1138

Using sub queries

I am trying to access a data in a table using a sub query. The table 1 contains a foreign key to table 2 , which means i can use that key to access the data in table 2. My problem is after i return the array list from the below shown method , the arraylist is null.

This is what i have done:

     LogEntry logBookDates;
    List<LogEntry> bookList =new ArrayList();
 try{
 PreparedStatement getSummaryStmt=con.prepareStatement("SELECT * FROM LOGENTRYTABLE WHERE DIARYCODE =(SELECT Diarycode FROM LOGBOOKTABLE WHERE STUDENTUSERNAME=? OR SUPERVISORUSERNAME=? AND PROJECT_APPROVE_STATUS=?)");

//the above statment is the sub query which i have created, i get the diary code from   log book table and then access the log entry table.



  getSummaryStmt.setString(1,userName);
  getSummaryStmt.setString(2,userName);
  getSummaryStmt.setString(3,"Accepted");
  ResultSet rs=getSummaryStmt.executeQuery();
  while(rs.next())
 {
     logBookDates=new LogEntry(rs.getString("STUDENTUSERNAME"),rs.getString("SupervisorUsername"),rs.getString("projecttitle"),rs.getString("projectDescription"),rs.getDate("startDate"),rs.getDate("enddate"),rs.getString("project_approve_status"),rs.getString("diarycode"),rs.getString("projectcode"),rs.getInt("Index"),rs.getString("log_Entry"),rs.getDate("logentry_date"),rs.getString("supervisor_comment"),rs.getString("project_progress"));
     bookList.add(logBookDates);
 }

 }catch(Exception e){}
 return bookList;
 }

I have not used sub queries before and this is the first time am using them. What seems to be the problem here ?

Thank you for your time.

Edit : Sample data of logbook table enter image description here

Sample Data of logentry table enter image description here

Expected output:

I don't have a screen shot of that but what i need is just to iterate through the arraylist which will be returned from the above method.

Upvotes: 2

Views: 86

Answers (1)

Linga
Linga

Reputation: 10553

Here is the problem, the LOGENTRYTABLE table doesn't contain a column with STUDENTUSERNAME, SupervisorUsername, projecttitle, projectDescription, startDate, etc...

 rs.getString("STUDENTUSERNAME"), rs.getString("SupervisorUsername"), etc...

probably, you need JOIN query

"SELECT * FROM LOGENTRYTABLE LT 
INNER JOIN LOGBOOKTABLE LB ON LT.DIARYCODE=LB.DIARYCODE
WHERE LT.DIARYCODE =
      (SELECT DIARYCODE FROM LOGBOOKTABLE
      WHERE (STUDENTUSERNAME=? OR SUPERVISORUSERNAME=?)
      AND PROJECT_APPROVE_STATUS=?)"

Upvotes: 2

Related Questions