Dan Lewis wrote:
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 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.
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
Hope this helps.
Your and Winston's explanations of joins cleared up a lot of my
misunderstanding of them. You can only get so much from books. The
fact that my using an Inner Join requires non-null values to get a
complete result set makes a lot of sense now. If I now understand
correctly, I should be using a Left Outer Join. That way I always get
the "table A" data in the result set, but "table B" data may be null if
there is no match to "table A". That is the response I was looking
for. I just tried it and it does give me the same result set
information (no records missing) as the Inner Join. However, I have no
Null values in "table A". It may take me a little while to test it with
Nulls in the Left ("table A") side. I also recant and apologize for my
"stupid" remark about SQL losing data. The reasons for the missing data
are now apparent to me and that remark was premature.
While I was doing this join editing testing on my sample query, I took
time to look at the order of joining in the Query Design window and it
looks like in order to get the main table (with foreign key) as "table
A", I had to first click on the "sub" table ("table B") element and then
drag to the "table A" element. In other words, it needs to be primary
key -> foreign key. My original response to Ian was not sure of this
drag order. In any case, the Join Properties dialog and prompt show the
correct order in the "Fields Involved" window, with "table A" in the
left column and "table B" in the right column.
Thanks for the help.
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