Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.