Hello Why don't you do it in this way:
SELECT * FROM Special_Offers WHERE IDCompany IN (SELECT IDCompany FROM Companies WHERE Region="Italy") Regards Maciek At 12:07 2002-05-16 +0200, you wrote: >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
