Zafif, The suggestions subsequent people on the forum provided would work (regarding grouping your query). Depending on your query, you can sometimes use the UNIQUE keyword to prevent having duplicate values/records returned, but I'm not sure if that would work for you, as I don't know your database schema. Either way, approaching your problem by using two result sets and looping through each to find the records you need to build your table is not an efficient way to approach this problem, not to mention the amount of memory you would be using on your server. You need to build your SQL query to return what you need if at all possible, and if not, then use additional logic in your JSP page to build your table(yes, I know, it doesn't follow the MVC pattern). If you have someone at your workplace who is proficient with SQL, you may want to obtain their help. Also, you can use the Query builder inside of SQLServer to help you build the SQL statement. Just open your database, go to the name of your projects database (in the left hand panes treeview), open the database, go to tables, and then right click on the parent table in question and select "open table, return all rows". Click on the second, third and fourth icons inside the table data view, "Show/Hide diagram pane", Show/Hide grid pane, Show/Hide SQL pane". To add additional child tables to the view, right click next to the graphical representation of the parent table in the diagram pane, and select "Add table". You can now begin building and testing your query to determine what result set is being returned by viewing the data in the results pane. The type of query is determined by the Query you select when you click on the 6th icon "Change Query Type". Once you get your query returning what you need, you can then copy it from the SQLPane and into your JSP page.
If you do not have direct access to SQLServer, then ask your network admin if he/she can create a DSN source for you to your projects database, and then you can use Access 2000 to "import" a copy of your SQLServer schema and the data it contains into your local Access version. You can then use Access's query builder to build you SQLstatements, and copy them into your JSP pages. Good luck! Celeste -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 2:01 PM To: JRun-Talk Subject: RE: Searching Resultset - my solution Celeste, I am using MS SQL Server and am using inner/outer joins. The reason why I am looping through two resultsets and not joining them, is: While I need one row for each projectid from the first resultset, I need to get all the rows with the same project id from the second resultset. When I do inner join, the resultset includes the duplicate data. Then instead of having multiple duplicate rows in the HTML table, with the same info except for few columns for each project x, having two resulset allows me to populate the first row with the first resulset row and then I am adding columns for the additional info for each projectid x from the second resultset. Thanks, Zeina "Haseltine, Celeste" To: Jrun-Talk <[EMAIL PROTECTED]> <CHaseltine@magt cc: icket.com> Subject: RE: Searching Resultset - my solution 02/26/2002 02:19 PM Please respond to jrun-talk Zafif, I have not really been following this discussion, so the suggestion I am about to make may have already been made. But it sounds to me like you are returning two result sets, and are looping through the second result set to find the records that relate back to a particular record in the first result set. Why are you doing this? Besides taking up a LOT of memory on your server, it's an inefficient way to code. A couple of suggestions here. 1. If you need the first result set to display a list for user selection, then do so. After the user selects the item he is interested in via your list, store the primary key value for the record your user selects in a session object. On the next "page", re-query your database using either an inner or outer join to return ONLY the primary record and its related records back to you, and then proceed on. 2. If you are NOT using a relational database on the back end that allows you to perform inner or outer join query's, consider using an XML file to initially store your data. You can query your database to obtain your records, write them out to an XML file onto your server, and then use that XML file as a "read only" version of your data for the duration of the session, making sure that you destroy it when your user logs out. Then you can then traverse the XML "tree" to find your primary record, and all it's related "child" records, IF you define your XML "tree" in this manner. In the event you are not familiar with inner/outer SQL joins, I have provided an example for you. In the below example I have a parent table called Orders and a child table called OrderDetails. The relationship between the two tables is one Order record to one or many OrderDetails records. In order to return a particular order record which contains ALL the related detail records for an Order with the primary key value of 2, I can query the db as follows: SELECT OrderDetails.OrderID, Orders.DateOrderPlaced, Orders.ConfirmationNumber, OrderDetails.TicketID, OrderDetails.Quantity, Orders.OrderID FROM OrderDetails INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID WHERE (OrderDetails.OrderID = 2) I don't know if this helps you any, but please re-evaluate your need to maintain two large result sets in memory. That's generally not a good way to code JSP's. Celeste -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 12:45 PM To: JRun-Talk Subject: RE: Searching Resultset - my solution Nikhil, tried to do what U suggested, but then for each entry in the Hashtable, I would have a vector, and since there are over 100 records...I will end up with over 100 vectors. I wasn't sure if that was efficient. What I am really trying to do is loop through one resultset A and in it, loop through another resultset B, and get the matching rows. So I tried searching through the resultset B by using the while(rsB.next()) and then finding the rows that I need, but the problem I had, jrun would not accept rsB.beforeFirst(), so I got data for the first row and not the rest of resultset A. Finally, I created 4 vectors, that each stored one of the columns of the resultset B, and if the column had a null value, I replaced it with '-'. Then searched through one of the vectors for the parameter and Vector vProj = new Vector(); Vector vODS_TYPE = new Vector(); Vector vPrevious_ODS = new Vector(); Vector vODS_Phased_Out = new Vector(); while (rsB.next()){ vProj.addElement(rsB.getString("SUB_PROD_ID") == null?"-":rsB.getString ("SUB_PROD_ID")); vPrevious_ODS.addElement(rsB.getString("Previous_ODS_to_be_Phased_Out") ==null?"-":rsB.getString("Previous_ODS_to_be_Phased_Out")); vODS_TYPE.addElement(rsB.getString("ODS_TYPE") == null?"-":sB.getString ("ODS_TYPE")); vPrevious_ODS.addElement(rsB.getString("Previous_ODS_to_be_Phased_Out") ==null?"-":rsB.getString("Previous_ODS_to_be_Phased_Out")) ; vODS_Phased_Out.addElement(rsB.getString("ODS_Phased_Out") ==null?" -":rsB.getString("ODS_Phased_Out"));} // I loop through the Resultset A, and populate a row of the table, then search for the parameter in one of the vectors (vProj). ------------------ ------------------- // for(int x = 0; x < vProj.size(); x++){ if((vProj.elementAt(x)).equals(Sub_Project_ID)){ counto++; %> <td class="ReportTableCellLabel"> <%=vODS_TYPE.elementAt(x)%> </td> <td class="ReportTableCellLabel"> <%=vPrevious_ODS.elementAt(x)%> </td> <td class="ReportTableCellLabel"> <%=vODS_Phased_Out.elementAt(x)%> </td> <% } } I don't know if this is the best solution, but that's the only way I could get it to work. When I first learned about the QueryTable Object, I thought it would include a method to search through the QueryTable obj. Thanks, Zeina Nikhil Silsarma <Nikhil.Silsarma@digit To: Jrun-Talk <[EMAIL PROTECTED]> alrum.com> cc: Subject: RE: Searching Resultset 02/26/2002 10:58 AM Please respond to jrun-talk Why don't you use Hashtable( key=parameter, value=another hashtable/or Vector). In that case, based on the key it will return the data). Nikhil R Silsarma Developer DigitalRUM UK Ltd. Tel: 020 7604 2049 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 26 February 2002 15:20 To: JRun-Talk Subject: Searching Resultset Drew, Great book! Went through it over the weekend! Wish I got it 6 months ago, when I started to develop in JSP! Could have saved me lot of time!! Anyway, my q for the day. I need to be able to search a resultset. The parameter that I need to use to search, is not unique in the Resultset and I need to be able to get all the entries with that key. I thought of using hashatable, but since the key is not unique, it won't work, I thought of using vectors of arrays. I looked into QueryTable and didn't find any methods to get by key. Is there an easy way to search the resultset based on a value? Thanks, Zeina ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
