Comment inline below.
On 01/08/2013 07:07 PM, Girvin R. Herr wrote:
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
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.
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 more.
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 hard to see
anyway. Seeing the "-" tells me the field is not NULL.
Hope this helps.
Ian Whitfield wrote:
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 all!!
I'm using PCLinuxOS 2012, LO Base 184.108.40.206 and MySQL 5.1.55
For unsubscribe instructions e-mail to: email@example.com
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