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

Hi :) 
So in the editing can you change the direction? or rather the way that the relationships works?
Regards from
Tom :)  

From: Girvin R. Herr <>
To: Tom Davies <> 
Cc: Dan Lewis <>; "" 
Sent: Thursday, 10 January 2013, 21:44
Subject: Re: [libreoffice-users] Re: Base scenario

Yes.  I confirmed that right-clicking on a join line does allow deleting or editing.
If I understand you correctly, no, relationship definitions should not be part of the back-end.  
The table relationship is defined for the back-end by the front-end through the SQL statements.  
For example, I have a table of suppliers, with names and addresses and other contact information.  
This table is related to almost all of my database (main) tables.  Additionally, each "main" table 
has its own set of table relationships with other (sub?) tables, most of which are for selecting 
options with a join.  Each record of these option tables contains a primary key and a text field 
for the option.  For example, I have a table of statuses for the item in the main table record.  
An integer foreign key in the main table contains a primary key value corresponding to the text 
element of the statuses table record.  That way, I am only storing an integer (key value) in the 
main table, rather than the option text, and with no repeated option text.  It
 also standardizes the option texts.  All of these multiple relationships must be defined by me - 
ergo it needs to be in the front-end.
Hope this helps clarify this.
Girvin Herr

Tom Davies wrote:
Hi :)
Can you right-click on a relationship's join-line and edit it's properties?  Shouldn't the 
relationships be part of the back-end rather than defined in the front-end?  Regards from
Tom :)  

From: Dan Lewis <>
To: Sent: Wednesday, 9 January 2013, 4:14
Subject: Re: [libreoffice-users] Re: Base scenario

Comment inline below.


On 01/08/2013 07:07 PM, Girvin R. Herr wrote:
Have you actually drawn any relationships?  Base will not do that for you.  Just adding the 
tables in the 'Relationships Window' will not create the relationships automagically.  You 
must click and hold on the one table element (remote key) and drag over to the related table's 
element (primary key), then release the mouse button.  Base will then draw a line between the 
two.  Note, the order of the drag is important.  It determines the type of join. Joins are 
confusing to me too, so I can't help much there.  I had to experiment with the direction to 
get it to work right.  I think it was remote key to primary key, but I am not sure of that any 

Warning!  The way the SQL language is set up, if either of the ends of a join (relationship) 
is NULL, then the record will be discarded and not show up in your result set.  No warnings, 
no errors.  Data records will just be missing.  IMHO, this is stupid (my mantra is: "thou 
shall not lose data"), but that is how the SQL language was set up.  So, make sure any joined 
data elements in all of your table records are not NULL.  Note that NULL is not zero (0) and 
vice-versa!  NULL means that there is no data in the record element.  I use a lot of remote 
keys in my database main tables that point to primary keys (options) in other tables.  In 
those other tables, I have made it a point to make the data elements of the first record to be 
"-", which is my equivalent of unknown, just to have something to select that is not NULL.  
You could probably use a blank (" "), but I prefer seeing the "-" in forms and reports.  Most 
times in reports, it is
  to see anyway.  Seeing the "-" tells me the field is not NULL.
Hope this helps.
Girvin Herr

       These statements about joins do not seem to be quite correct. What you are describing is 
an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the 
primary key have a value.
      Suppose we have two tables A and B and that the foreign (remote) key is in table A and the 
primary key is in table B.
     Example 1: table A Left Outer Join table B. The output (result set) for this contains all 
the fields in table A and their values on the left side of the combined table. The right side 
contains all the fields in Table B. The rows in which the primary key value matches the foreign 
key value, data from both table appear in the output. However, where there is no primary key 
value in table B that matches the foreign key value in table A, all the fields from table B for 
that row will be NULL.
      Example 2: table A Right Outer Join table B. The output for this contains all the fields 
in table B and their values on the right side. For each output row in which the foreign key does 
not have a value that matches any value of the primary key, the fields in the left side of it 
will be NULL.
      Example 3: table A Cross Join table B. This is also referred to as a Cartesian Product. In 
this case, each row of table A is joined to all the rows of table B. This contains all of the 
possible combinations of combining both tables. Usually, some rows of the output will have the 
table A fields all showing NULL while others will have the fields of table B showing all NULL.

Ian Whitfield wrote:
Hi All

Re - My previous post.... Have been doing some Googling etc and found the 'Relationships 
Window' for setting Relationships.

I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting lines or set 
any Relationships!!??

Is this another "Gotcha" of using MySQL and Base together? As it does _NOT_ seem to work at 

I'm using PCLinuxOS 2012, LO Base and MySQL 5.1.55

Pretoria RSA.

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


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.