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
