Or perhaps something like: select a.*, b.* // Better to select individual fields from each table than a.* or b.* from Companies a, Special_Offers b where a.IDCompany = b.IDCompany and a.IDCompany IN (select IDCompany FROM Companies where Region="Italy")
If you have to join the two tables, and you want to limit it to one query only, you are going to get the company data duplicated. In effect you are de-normalising the data. -----Original Message----- From: Luca Ventura [mailto:[EMAIL PROTECTED]] Sent: 16 May 2002 12:12 To: [EMAIL PROTECTED] Subject: R: Retrieve data in Microsoft Access (off-topic) Yes, I thought to this solution but there is this problem: I need to have the data about the companies too: so first I would have to execute a query like this: select * from Companies where Region="Italy"; and after that I must execute the query you told: SELECT * FROM Special_Offers WHERE IDCompany IN (SELECT IDCompany FROM Companies WHERE Region="Italy") But before excuting the second query someone could insert or remove data (rows) from the table "Companies": in this case the subselect "SELECT IDCompany FROM Companies WHERE Region="Italy" of the second query would return a different number of values for "IDCompany" of those I obtained in the first query. To solve this problem I could use "transaction" and to block the two tables before the execution of the two queries terminates: anyway in this case I would have bad performance...are you agree? Let me know, please. Thanks a lot. Luca -----Messaggio originale----- Da: A mailing list for discussion about Sun Microsystem's Java Servlet API Technology. [mailto:[EMAIL PROTECTED]]Per conto di Maciej Borkowski Inviato: giovedi 16 maggio 2002 12.38 A: [EMAIL PROTECTED] Oggetto: Re: Retrieve data in Microsoft Access (off-topic) 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 ___________________________________________________________________________ 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 This e-mail is confidential and intended only for the addressee(s) shown. If you are not an intended recipient, please be advised that any use, dissemination, forwarding or copying of this e-mail is strictly prohibited. Internet e-mails are not necessarily secure and the AXA Group does not accept responsibility for changes made to this message after it was sent. Please note that incoming and outgoing electronic mail messages may be monitored. Should you receive this transmission in error, notify the sender immediately. Thank you ___________________________________________________________________________ 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
