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

On Sat, Jan 2, 2016 at 4:36 AM, Luuk <> 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

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

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,

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?

"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 <>
GPG Key ID: F5E179BE

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.