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

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