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

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

     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.

--Dan

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