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
