Re: [libreoffice-users] Re: LOBase Query - Left or Right Join?

On 01-01-16 22:08, Paul Steyn wrote:
> Hi Don,
>
> Your SELECT statement is indeed incorrect; it seems as though you don't
> fully understand joins (I could be mistaken, but your syntax is off
> by enough to suggest this).
>
> Firstly, choose a format for your SELECT statement to make it easier to
> read. I've reformatted it below using one such formatting standard that
> I've used in the past, but you can of course choose your own. The
> important thing is that it isn't simply one large blob of text.
>
> Secondly, use table aliases. After the table name in the FROM clause,
> you can include a table alias, which you can use elsewhere (including in
> the SELECT clause) to refer to the table. These are often much shorter
> than the table names, making the whole statement easier to read. Also,
> if you are including a table more than once (for different join
> conditions), I think you are required to have aliases to distinguish
> between the two table uses. You are actually doing this in your
> statement, and it gives rise to an ambiguity.
>
> Thirdly, JOINs are complicated, as there are many options: INNER
> JOINs, LEFT, RIGHT and FULL OUTER JOINs, CROSS JOINs, NATURAL JOINs,
> etc.
>
> https://en.wikipedia.org//wiki/Join_(SQL)
>
> The most common (in my experience) are INNER and LEFT OUTER JOINs.
> Don't worry about the rest for now.
>
> Think of it this way:
>
> For inner joins, with the condition in the FROM clause
>
> (i.e. FROM tableA a INNER JOIN tableB b on a.id = b.id)
>
> You are adding all rows from tableA to a result set, then, for each row
> in tableB that matches the condition, you are adding all the fields
> from tableB to that row of the result set (if more than one row matches
> a row in the result set, the existing row is duplicated). If a row in
> the result set doesn't match any rows in tableB, it is removed from the
> result set.
>
> For OUTER JOINS, of the form:
>
> FROM tableA a LEFT OUTER JOIN tableB b on a.id = b.id
>
> You are doing the same thing, except that should a row in the result
> set not match any rows in tableB, it is not discarded from the result
> set, and instead NULL values are used for all fields that would
> otherwise have come from tableB.
You are absolutely right, but the *syntax* is different in LO ....
see link i posted earlier

--
To unsubscribe e-mail to: users+unsubscribe@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