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

Tom,
I can't seem to change the direction from the editing (Join Properties) dialog. If the direction needs to be changed, I have been selecting the line by right-clicking on it and selecting the "Delete" option. I then re-enter the relationship (Join) in the opposite direction as I had explained in my last posting to Dan et al.
The type of Join can be changed in the right-click "Edit" option under the "Join Properties" dialog. There is a "Type" list box, which has the Inner, Left, Right, or Cross Join options. Under that list box in the dialog, there is a table labeled "Fields involved", with two columns, left and right, corresponding to the left and right join ends. Under that panel, is a hint of sorts, that explains the Join type, the direction, and that warning about the join may not be supported, for all but when "Inner" is selected in the list box. There is another radio button labeled "Natural" for a "Natural" Join augmentation, but I am not familiar with that option. 

Hope this helps.
Girvin Herr



Tom Davies wrote:

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 <girvin.herr@sbcglobal.net>
    *To:* Tom Davies <tomdavies04@yahoo.co.uk>
    *Cc:* Dan Lewis <elderdanlewis@gmail.com>;
    "users@global.libreoffice.org" <users@global.libreoffice.org>
    *Sent:* Thursday, 10 January 2013, 21:44
    *Subject:* Re: [libreoffice-users] Re: Base scenario

    Tom,
    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 <elderdanlewis@gmail.com
    <mailto:elderdanlewis@gmail.com>>
    >> To: users@global.libreoffice.org
    <mailto:users@global.libreoffice.org> Sent: Wednesday, 9 January
    2013, 4:14
    >> Subject: Re: [libreoffice-users] Re: Base scenario
    >>
    >> Comment inline below.
    >>
    >> --Dan
    >>
    >> On 01/08/2013 07:07 PM, Girvin R. Herr wrote:
>> >>> Ian, 
    >>> 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. 
    >>> 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 all!!
    >>>>
    >>>> I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55
    >>>>
    >>>> IanW
    >>>> Pretoria RSA.
    >>>>
>>>> >> -- For unsubscribe instructions e-mail to: 
    users+help@global.libreoffice.org <mailto: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
    >>
    >>
    >>
    >>
>> 




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

Context

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.