Displaying one to many records from DB

Hello,

There are many dept record in my db and each dept may have one or more than one record.
I am trying to fetch one dept and then to get this dept records that may be one or many. Please check this link that shows the wrong output whereas the expected output is at this Link

Here is my all code of the method

public String viewShift(String userId)throws SQLException{
		
	String msg="", msg1="", msg2="";
	
	try{
		if ((cnn==null)||cnn.isClosed()){
		cnn=DB.getOracleConnection();
		}
	
		int count=0; String color="";
		
		String query = 	" select unique(a.dept_id),deptName from dept a, shiftManager b "+
				" where a.dept_id=b.dept_id and c_id=? order by deptName asc";

String query1= 	" select s_id,title,to_char(startTime,'HH24:MI'),to_char(endTime,'HH24:MI') "+
				" from shiftManager where dept_id=? order by startTime asc";

PreparedStatement ps=cnn.prepareStatement(query);		
		ps.setString(1, userId);

rs=ps.executeQuery();
		while(rs.next()){
			
			String deptId 	= rs.getString(1);
			String deptName	= rs.getString(2);						
		msg1=
			"<table border=0 >"+
			"<tr >"+
				"<td  class=whiteFont>   "+deptName+"</td>"+			
			"</tr>";
		
		PreparedStatement ps1=cnn.prepareStatement(query1);		
		ps1.setString(1, deptId);

rs1=ps1.executeQuery();
		while(rs1.next()){

String sId	= rs1.getString(1);
			String title	= rs1.getString(2);
			String startTime= rs1.getString(3);
			String endTime	= rs1.getString(4);

msg2+=

"<tr><td  >"+
			"<table border=0 >"+
			"<tr>"+
				"<td class=normalFont>"+title+"</td>"+
				"<td class=normalFont >"+startTime+"</td>"+
				"<td class=normalFont >"+endTime+"</td>"+

"<td  >"+
				"<a >"+
				"<img  border=0  Click to Edit\"></a>"+
				"</td>"+

"<td  >"+
				"<a >"+
				"<img  border=0  Click to Delete\"></a>"+
				"</td>"+
			"</tr></table>"+
		"</td></tr>";
		}			
		ps1.close();

msg+=msg1+msg2+"</table><br>";	
		}	
		ps.close();

}catch(Exception e){
	System.out.println("ShiftDAO.ViewShift()"+e.toString());
	}	
	
	return msg;

}//

Thanks & best regards

Can anybody please help me to fix the problem?

Best regards

Can you please try closing rs1? Looks like since you are not closing rs1 so previous department id's are also getting added next time....try closing rs1 once hopefully it will solve the issue.....

Please let me know the result.....

close rs1 just after closing ps1....

Since you are reusing the variable "m2" for both the departments, it is retaining the values of the first department and carried to the second department. Try to reinitialize the variable m2 after the iteration of the first department is over.

Regards
Santhosh

Dear Santhosh,

Thanks a lot. The problem fixed with initializing msg2 within while loop

Appreciated!!

Best regards