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