Can you explain me better please? What does the clause "order by" has to do
with
the java data structure "Vector" or "Hashtable"?
Thanks,
Luca
-----Messaggio originale-----
Da: A mailing list for discussion about Sun Microsystem's Java Servlet
API Technology. [mailto:[EMAIL PROTECTED]]Per conto di
RBonazzo
Inviato: gioved� 16 maggio 2002 12.24
A: [EMAIL PROTECTED]
Oggetto: R: Retrieve data in Microsoft Access (off-topic)
Priorit�: Alta
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
___________________________________________________________________________
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
