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

On Sat, Jan 2, 2016 at 4:36 AM, Luuk <luuk34@gmail.com> wrote:

> 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).
> >
> > Thanks Paul,  I'll acknowledge that my use of explicit JOIN statements is
limited - this is probably one of the most complicated queries I've done so
far.



> 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.
> >
> > I really should have formatted - sorry about that.



> 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.
I do normally use aliases, but I really had not gotten that far with this
query - just trying to sort of get it written down.  Oops.

> > Thirdly, JOINs are complicated, as there are many options: INNER
> > JOINs, LEFT, RIGHT and FULL OUTER JOINs, CROSS JOINs, NATURAL JOINs,
> > etc.
> >
> >
> > I think I have a basic grasp, but not the practical implementation -
particularly on such a complicated query as this one.  I am returning to an
old project after two years of really not thinking much about the joins,
etc.



> You are absolutely right, but the *syntax* is different in LO ....
> see link i posted earlier
I have tried this query, which produces a "Column Not Found" error.  Note
that I removed the other tables (from my OP) regarding the contact info,
just to try and keep it relatively simple at the moment.  I wonder if my
placement of the '{oj}' bit might make a difference?

SELECT
"tCoreCategory"."category" "Category",
"tCntEntity"."entity_name" "Entity",
"tCntPerson"."first_name" "First Name",
"tCntPerson"."last_name" "Last Name",
"tCntAddress"."location_name" "Location",
"tCntAddress"."address1" "Address"
FROM { oj
"tCntEntityCategory" "ent_cat"
JOIN "tCntEntity" "ent"
  ON "ent_cat"."entity_id" = "ent"."entity_id"
JOIN "tCoreCategory" "core_cat"
  ON "ent_cat"."category_id" = "core_cat"."category_id"
  AND "core_cat"."category" LIKE 'Internal -%'
JOIN "tCntEntityPerson" "ent_pers"
  ON "ent_pers"."entity_id" = "ent"."entity_id"
JOIN "tCntPerson" "pers_from_ent"
  ON "ent_pers"."person_id" = "pers_from_ent"."person_id"
LEFT OUTER JOIN "tCntContactAddress" "ctct_addr"
  ON "ctct_addr"."entity_id" = "ent"."entity_id"
LEFT OUTER JOIN "tCntPerson" "pers_from_addr"
  ON "ctct_addr"."person_id" = "pers_from_addr"."person_id"
LEFT OUTER JOIN "tCntAddress" "addr"
  ON "ctct_addr"."address_id" = "addr"."address_id" }



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

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