The query you told me is equivalent to:
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.Region="Italy"
And this is more simple query...are you agree? :-)
Anyway in both cases I would have data about companies replicated!
How can avoid this problem? Have you any solution?
What do you mean with "de-normalising the data"?
Thanks a lot in advance!
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 13.17
A: [EMAIL PROTECTED]
Oggetto: Re: Retrieve data in Microsoft Access (off-topic)
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
___________________________________________________________________________
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
