Hi try to insert the result in some vector or hashtable To have the data in order use the clause order by
Regards Rinaldo -----Messaggio originale----- Da: A mailing list for discussion about Sun Microsystem's Java Servlet API Technology. [mailto:[EMAIL PROTECTED]] Per conto di Luca Ventura Inviato: gioved� 16 maggio 2002 12.08 A: [EMAIL PROTECTED] Oggetto: Retrieve data in Microsoft Access (off-topic) Hello everybody! I hope someone can help me to solve this problem (with an efficient solution) and I excuse with everybody if it is "off-topic" but I posted it in many other mailing lists receiving no reply an I have a great need to solve it...:-( I use Microsoft Access as database and a JDBC-ODBC driver to access to it. I don't know how to retrieve all the data I need in an efficient way from the tables I defined. The tables stores information about some companies and the special offers for sale they have for their clients. The tables are (in pseudo-syntax): TABLE "COMPANIES": CompanyName: String IDCompany: long --> Primary key Address: String Region: String .. (other information) TABLE "SPECIAL_OFFERS" NameProduct: String IDCompany: long --> Foreign key references "COMPANIES" PriceOffer: double [price in euro] PriceProduct: double [price in euro, it is greater than PriceOffer] ..(other information) Of course a company can have and sell more special offers (one to many relation) for sale but a special offer belongs only to a company (one to one relation). I want to be able to retrieve from the database all the information about some particular companies (for example all the companies that are present in a given region) and for each of them the special offers they have. I though to the following solutions: 1) Execute a query like this: select * from Companies, Special_Offers where Region="Italy" and Special_Offers.IDCompany=Companies.IDCompany; The problem of this solution is that I would obtain as result a table with the information about a company repeated as many times (and put in different rows) as the number of the special offers it has, like this: CompanyName IDCompany Address Region ... NameProduct IDCompany PriceOffer PriceProduct... Company 1 1 Address1 Region1 Product1 1 120.32 150.23 Company 1 1 Address1 Region1 Product2 1 110.32 140.23 Company 1 1 Address1 Region1 Product3 1 10.32 20.23 And if I have thousands of companies and many special offer for each of them this solution waste a lot of memory beacuse many information are repeated. 2)Execute two different queries: the first one to retrieve all the information about the companies I need (from the table Companies) and the second one to retrieve the special offers of each of them (from the table Special_Offers, using the ID of each company). Here I have two problems: a) Where can I store all the IDs of the Companies that I obtained executing the first query (consider that I can have thousands of IDs)? In memory or in the disk? b) I would have to execute a query using the IDs obtained from the first query, like this: select * from Special_Offers where IDCompany=1 OR IDCompany=5 OR IDCompany=6 ... IDCompany=876... 1,5,6,876 are some of the IDs I obtained from the first query. The problem is that I can have thousands of IDs and for this reason the query string to execute would be very long and I don't know if it would be able to execute it. To avoid this problem I could execute so many queries as the IDs I have but this solution would be very inefficient.... Besides a very IMPORTANT THING: I would like to have the results of the second query in the same order of the IDs I obtained from the first query. I mean: if the first query gave me the ID of company1 (eg: 123) and then that one of the company2 (e.g: 75) between the results of the second query I would like to have the special offers of Company1 (with ID number 123) before that ones of Company2 (with ID number 75). Thanks a lot to everybody in advance! Luca ________________________________________________________________________ ___ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
