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

Jay Lozier wrote:
> 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)

      Sorry, but this does not give the same results as my query.  Yours will give the
date the meter is read and the reading for each date in the "Reading" table for the two
listed in the WHERE clause. The question the query is suppose to answer is How many KWH am
I using each month? Your query does not answer this question. In my table, I have 41 rows
of data. When I run my query, I get 40 rows as my output. Your query has only 2 rows in
the output.
     Here is the first 4 rows of the table.

ID      End Date        Ending
1       04/16/09        16717
2       05/15/09        17125
3       06/17/09        17418
4       07/20/09        17735

     I Added some things to my query:
SELECT "Beginning"."Date", "Ending"."Date", DATEDIFF( "Ending"."Date", "Beginning"."Date" 
) AS "Days", "Ending"."Meter" - "Beginning"."Meter" AS "KWH used", ( "Ending"."Meter" - 
"Beginning"."Meter" ) / DATEDIFF( "Ending"."Date", "Beginning"."Date" ) AS "KWH/Day" FROM 
"Power"."Reading" AS "Beginning" LEFT JOIN "Power"."Reading" AS "Ending" ON "Ending"."ID" 
= "Beginning"."ID" + 1

WARNING: This was done using Base as the front end and MySQL 5.1.x as the back end. If you 
try this using a Base embedded database, you have to change the DATEDIFF() to
     DATEDIFF('dd', "Ending"."Date", "Beginning"."Date")

     The results of my "new" query are:
Date              Date         Days  KWH used   KWH/Day
04/16/09        05/15/09        29      408     14.069
05/15/09        06/17/09        33      293     8.8788
06/17/09        07/20/09        33      317     9.6061

> > 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

     Basic theory: relational databases are based upon Algebra including the Algebra of
Sets (unions, intersections, Cartesian products, Subtraction of sets, etc.). In fact some 
of the early complaints about the structure of relational databases was that it was too 
mathematical.
     Observations: JOIN ON clause is based upon the Algebra of Sets. The ON expression is 
algebraic. The WHERE clause is also algebraic.
     My point: If there is an algebraic expression that describes the relationship 
between a primary key and its foreign key, then that expression can be used as the ON 
expression. If further restrictions are needed, then these belong in the WHERE clause.
     This is about the theoretical rather than what has been done. I'm looking at the 
possible rather than the traditional.



--
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