Hello!
My problem is not to show results (at least until now...) but how to execute
the queries and what is the best solution to retrieve the data I need.
I think that to use only a table to store all the data is not a good
solution
because this means to repeat many times the same data.
Let's suppose the scheme of my database is OK (that is to say the
definition of the tables): what queries do you execute to get the data
I told you? Have you a new solution? If not, which of the two solution I
said you would use?
Thanks,
Luca
-----Messaggio originale-----
Da: A mailing list for discussion about Sun Microsystem's Java Servlet
API Technology. [mailto:[EMAIL PROTECTED]]Per conto di
PEARCE Robert
Inviato: gioved� 16 maggio 2002 12.17
A: [EMAIL PROTECTED]
Oggetto: Re: Retrieve data in Microsoft Access (off-topic)
You can never display thousands of items of data at once, so consider a
system whereby only a subset is on view, and of this subset only the summary
data is shown (i.e. enough to tempt a user to click on it to go further).
The user can then use next/previous functionality to browse futher.
If display is not an issue, the only solutions I can think of are:
i) Good indexing on the tables (Access?). Not sure how it will affect
pulling all the data
ii) Cacheing data
iii) De-normalising the data into a reporting table, i.e. one table holding
all the data, updated from the 'main' tables by triggers or timed update
scripts. This at least avoids a join.
Sooner or later you have to get around the problem of pulling thousands of
records from a db over the network
-----Original Message-----
From: Luca Ventura [mailto:[EMAIL PROTECTED]]
Sent: 16 May 2002 11:08
To: [EMAIL PROTECTED]
Subject: 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
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
___________________________________________________________________________
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
