Mark Stanton wrote:
As far as I remember it's explicitly stated in the SQL definitions
Id fields should not contain data entered by users
You can't rely on them not least because often they're automatically generated by the
system. They bear no necessary relationship to the data.
Somewhere there is disconnect. We seem to be talking at each other.
The table I put in my earlier email had three fields: the primary key, ID, which was
automatically generated by Base, the date the meter was read, and the reading. The field,
ID never has contained data entered by me. It is in First Normal, Second Normal, etc. Form.
When use a left join to join this table to itself, the first three fields of each
row are the three fields of the table and the values of the ID field are unique. What
about the other three fields of each row of the query? For each row, ID in the fourth
column is always one larger than the ID in the first column.
What relationship does the values of ID have with the values of the dates and the
meter readings? None. I'm only using the two ID fields to place the rows of data where I
want them to be.
It so happens that the ID field consists of integers. More importantly, being
integers, they can be used to order the rows. This is want I did with them: order the
data. I just used arithmetic to say what that order is.
Concerning the query that I posted earlier: the values I got using it are identical
to the values I have in a spreadsheet to track these statistics. I have 41 rows in the
table resulting with 40 rows of query output. That is 200 cells containing data which
match to the data in the spreadsheet.
Here is where I think we are not paying attention to each other. The usual ON
expression involves setting the primary and foreign key as equal. I'm saying that there
are situations that arise between tables in which equality in the primary-foreign key pair
is not what is required.
I have given an example for which a simple arithmetic equation works. In fact, I
think there is a class of queries that fit the case: "primary key" = "foreign key" + n
where n is a positive integer and the table is joined to itself. When a company compares
weekly, monthly quarterly, or annual sales year over year, they are going to use something
very similar to this. This can be used to determine quarter over quarter, etc. as well.
How else do you create a query that has weekly data for a store for last year on the left
and the data for the same store for this year on the right? [I would use "foreign key" =
"primary key" + 52 in a LEFT OUTER JOIN.]
Now as far as using more complex algebraic expression in a JOIN, I agree that this
is a "little far out". It really depends upon how the primary and foreign keys are generated.
For example, the primary key could be the positive integers (counting numbers). The
foreign key could be the cubes of the positive integers. Then if we want to join the rows
of one table with the rows of another table so that the foreign key is the cube of the
primary key, we would have to use "foreign key" = "primary key" cubed in the ON
expression. Now I agree we don't usually use the cube of positive integers as the values
of a foreign key. So this is definitely "far out". My point is that it does meet the
requirements for a query that could produce useful information. Just because the first
four values for the foreign key is 1, 8, 27, and 64, that does not mean these are not
distinct values that could not be used to order the rows of a table by them.
For the most part, people will continue to use positive integers with unit
increments in their primary keys. This means the foreign keys will have the same file
type. For them, what I have written will probably make little sense. And they may not need
any of the other possibilities. And I also agree, most of these possibilities serve no
real purpose based upon how we do things now. Some of these primary key properties we have
mentioned might be very difficult to impossible to define for a table.
From the beginning, my only purpose is to question as to what is possible given the
characteristics of the primary and foreign keys. Again, I realize that what is possible
may not be what we would want to do. It may not serve a useful purpose even though it
works and it follows all the rules of SQL.
For unsubscribe instructions e-mail to: firstname.lastname@example.org
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy