Re: [libreoffice-users] Other SQL question as it affects Base

On 07/29/2012 08:08 PM, Dan wrote:
> Mark Stanton wrote:
> > Keys are intended for the internal workings of the database, they are 
> > not meant to be
> > used like this.
> >
> > What you want is the WHERE clause, or occasionally the HAVING clause, 
> > matching or
> > excluding rows based on their data.
> >
> > Keys are NOT data and should not be used as such.
> >
> > Regards Mark Stanton One small step for mankind...
>
>      I have a table, Reading, that contains three fields: ID, Date, 
> and Meter. Date is the
> date the meter is read, and Meter is the meter reading.
>      I want to know how much electricity (KWH used) I have used each 
> month. The query below will provide that information:
>
> SELECT "Ending"."Date", "Ending"."Meter" -"Beginning"."Meter" AS "KWH 
> Used" FROM "Power". "Reading" AS "Beginning" LEFT JOIN "Power". 
> "Reading" AS" Ending" ON "Ending"."ID" =1 + "Beginning"."ID"
Try the following

SELECT date, meter
FROM    Reading
WHERE    date = initial date OR date = ending date (date = '4/1/2012' OR 
date = '4/30/2012' - using correct date format for your system)
>      Perhaps you are right about keys being intended for the internal 
> workings of the database. While I don't have an example of one key 
> being the multiple of the other key, it is possible. It just might not 
> have any practical applications.
Keys are intended in an relational database to uniquely identify each 
row of data. While often being integers any unique string or group of 
columns can be a key. For example, if there is only one date entry for 
each date you could use the date as a key. The reason integers are 
typically used is that they allow the possible multiple entry of a date 
in a table because each entry would assigned a different integer.  The 
key value id = 100 has no required mathematical relation to the key 
value id = 200, mostly likely all this means is there are 100 entries 
since 100th entry. Otherwise there may no connection be the two entries.

For example, two companies could have the same name - Fred's Wholesale 
Produce but be located in different states (Kentucky and Indiana for 
example). A key of company name and state is possible but often awkward 
to use when accessing other tables such as orders or purchases. Integer 
id's allow for accessing other tables by using on field which happens to 
a unique integer.

An Orders table might have the following initial columns: id, id_vendor, 
id_PO_items PO, Date etc. The id is a unique key for the Orders table, 
the id_vendor refers to the id in a separate vendor table which might 
have the vendor contact information. The Id_PO_items might refer to a 
table which lists each item order with appropriate vendor information.
> --Dan


--
Jay Lozier
jslozier@gmail.com


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted