Re: [libreoffice-users] Re: Base scenario

Ian,

Below, Girvin had a warning about joins. Specifically, he was talking 
about inner joins.

SQL has different types of joins. Below, I explain them. Note: It has 
been some time since I used SQL. I might have errors.

Sorry if you already know this information. But possibly it will be 
helpful to other people.

Suppose that we join two tables, called "table_a" and "table_b".
Suppose that we join these two tables on columns "column_a" and "column_b."
Our join statement will looking something like:

    SELECT *
    FROM table_a INNER JOIN table_b
    ON table_a.column_a = table_b.column_b;

Let "a" be any row in table_a that "satisfies the join condition."
Let "b" be any row in table_b that "satisfies the join condition."
Below, the text "INNER" and "OUTER" are optional when you type the joins 
in SQL.

1. An INNER JOIN gives you rows of the form (a, b).

2a. A LEFT OUTER JOIN gives you rows of the form (a, b) and (a, NULL).

2b. A RIGHT OUTER JOIN gives you rows of the form (a, b) and (NULL, b).

2c. A FULL OUTER JOIN gives you rows of the form (a, b), (a, NULL), and 
(NULL, b).

3. A CROSS JOIN is a Cartesian product between the rows of table_a and 
the rows of table_b. It gives you rows of the form (aa, bb), where aa is 
any row in table_a, and bb is any row in table_b.

Winston

On 01/08/2013 10:55 PM, Winston Chuen-Shih Yang wrote:
> Ian,
>
> Below, Girvin had a warning about joins. Specifically, he was talking 
> about inner joins.
>
> SQL has different types of joins. Below, I explain them. Note: It has 
> been some time since I used SQL. I might have errors.
>
> Sorry if you already know this information. But possibly it will be 
> helpful to other people.
>
> Suppose that we join two tables, called "table_a" and "table_b".
> Suppose that we join these two tables on columns "column_a" and 
> "column_b."
> Our join statement will looking something like:
>
>     SELECT *
>     FROM table_a INNER JOIN table_b
>     ON table_a.column_a = table_b.column_b;
>
> Let "a" be any row in table_a that "satisfies the join condition."
> Let "b" be any row in table_b that "satisfies the join condition."
> Below, the text "INNER" and "OUTER" are optional when you type the 
> joins in SQL.
>
> 1. An INNER JOIN gives you rows of the form (a, b).
>
> 2a. A LEFT OUTER JOIN gives you rows of the form (a, b) and (a, NULL).
>
> 2b. A RIGHT OUTER JOIN gives you rows of the form (a, b) and (NULL, b).
>
> 2c. A FULL OUTER JOIN gives you rows of the form (a, b), (a, NULL), 
> and (NULL, b).
>
> 3. A CROSS JOIN is a Cartesian product between the rows of table_a and 
> the rows of table_b. It gives you rows of the form (aa, bb), where aa 
> is any row in table_a, and bb is any row in table_b.
>
> Winston
>
> 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
> >
> >
> >
> > 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
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