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

2016-01-01 Thread Luuk 



On 01-01-16 13:08, Don Parris wrote:

Hi all,

I have a set of tables for contacts, and a somewhat complex query that
seeks all the available contact information on a subset of the contacts.
By "incomplete result set", I mean I have one contact that does not show up
in the query results, but should.

I know why: that contact has phone and e-mail information, but does not yet
have a record in the address table.  Everyone else who has an address
record in the address table shows up in the results.  I would like to see
what information *is* available (regardless of whether or not there is an
address record (or even if there is an address, but no phone/email
information).

I am fairly sure I need to change the join type (using LEFT or RIGHT) on
the relevant table(s), just not sure how exactly.  My current query is
(built in Design View, copied from SQL View):





In design mode, right-click on the link between two tables, and click 
'edit'...



If you want to manually edit the SQL, the syntax is shown here:
https://help.libreoffice.org/Common/Special_Settings#Use_Outer_Join_syntax_.27.7BOJ_.7D.27


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

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

2016-01-01 Thread Don Parris 
Hi all,

I have a set of tables for contacts, and a somewhat complex query that
seeks all the available contact information on a subset of the contacts.
By "incomplete result set", I mean I have one contact that does not show up
in the query results, but should.

I know why: that contact has phone and e-mail information, but does not yet
have a record in the address table.  Everyone else who has an address
record in the address table shows up in the results.  I would like to see
what information *is* available (regardless of whether or not there is an
address record (or even if there is an address, but no phone/email
information).

I am fairly sure I need to change the join type (using LEFT or RIGHT) on
the relevant table(s), just not sure how exactly.  My current query is
(built in Design View, copied from SQL View):

SELECT "tCntPerson"."first_name" "First Name", "tCntPerson"."last_name"
"Last Name", "tCoreCategory"."category" "Category",
"tCntEntity"."entity_name" "Entity", "tCntAddressType"."address_type" "Addr
Type", "tCntAddress"."location_name" "Location", "tCntAddress"."address1"
"Address", "tCntAddress"."locality" "Locality",
"tCntRegion"."region_postal" "Region", "tCntAddress"."postcode" "PostCode",
"tCntCountry"."country_un" "Country", "tCntContactInfo"."priority" "Cnt
Priority", "tCntContactMethod"."contact_method" "Method",
"tCntContactInfo"."contact_info" FROM "tCntEntityCategory", "tCntEntity",
"tCntEntityPerson", "tCntPerson", "tCoreCategory", "tCoreType",
"tCntContactAddress", "tCntAddress", "tCntRegion", "tCntCountry",
"tCntContactInfo", "tCntContactMethod", "tCntPersonContactInfo",
"tCntAddressType" WHERE "tCntEntityCategory"."entity_id" =
"tCntEntity"."entity_id" AND "tCntEntityPerson"."entity_id" =
"tCntEntity"."entity_id" AND "tCntEntityPerson"."person_id" =
"tCntPerson"."person_id" AND "tCntEntityCategory"."category_id" =
"tCoreCategory"."category_id" AND "tCoreCategory"."type_id" =
"tCoreType"."type_id" AND "tCntContactAddress"."entity_id" =
"tCntEntity"."entity_id" AND "tCntContactAddress"."person_id" =
"tCntPerson"."person_id" AND "tCntContactAddress"."address_id" =
"tCntAddress"."address_id" AND "tCntAddress"."region_id" =
"tCntRegion"."region_id" AND "tCntAddress"."country_id" =
"tCntCountry"."country_id" AND "tCntContactInfo"."contact_method_id" =
"tCntContactMethod"."contact_method_id" AND
"tCntPersonContactInfo"."contact_info_id" =
"tCntContactInfo"."contact_info_id" AND "tCntPersonContactInfo"."person_id"
= "tCntPerson"."person_id" AND "tCntAddress"."address_type_id" =
"tCntAddressType"."address_type_id" AND "tCoreCategory"."category" LIKE
'Internal -%' ORDER BY "Last Name" ASC, "Entity" ASC, "Cnt Priority" ASC

The most relevant tables (for this query) are:
<> tCntEntity (Business or last name)
<> tCntPerson (a person can be associated with many entities/addresses)
<> tCntAddress
<> tCntContactAddress (links the corresponding entity and person with a
specific address)

Quick example (that I hope helps with understanding the design logic):
John Jones belongs to an entity called "Jones" that has a home address.  He
also belongs to an entity called "Widget Corp" that has a business
address.  My query should pull up Mr Jones regardless of whether there is
an address record affiliated with the "Jones" entity.

Thanks in advance!
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

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

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

2016-01-01 Thread Don Parris 
On Fri, Jan 1, 2016 at 10:59 AM, Luuk  wrote:

>
>
>
> 
>>
>>
>>
>
> In design mode, right-click on the link between two tables, and click
> 'edit'...
>
>
> If you want to manually edit the SQL, the syntax is shown here:
>
> https://help.libreoffice.org/Common/Special_Settings#Use_Outer_Join_syntax_.27.7BOJ_.7D.27
>
>
>

Thanks, but neither of these is very helpful.  Editing the relationships
only allows me to set the CASCADE and DELETE options (unless I am missing
something, in which case maybe a little further guidance might help.)

Following the link, I tried to set the special settings, but don't have any
option as suggested - using LO 4.4.7.2 (standard LOBase internal database)
on Manjaro Linux here.

I have only 3 special settings available via the "Edit-Database-Advanced
Settings" as suggested:
<> End Text Lines with CR+LF
<> Form Data input checks for required fields
<> Use ODBC conformant date/time fields.

There is no option whatsoever to allow to use outer join syntax.

Regards,
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

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