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

Update:  I did try re-writing the query with LEFT/RIGHT JOINS, but all I
get are syntax errors.  Maybe I have my SQL statement incorrect?

SELECT "tCoreCategory"."category", "tCntEntity"."entity_name",
"tCntPerson"."first_name", "tCntPerson"."last_name",
"tCntAddress"."location_name", "tCntAddress"."address1"
FROM "tCntEntityCategory" JOIN "tCntEntity" ON
"tCntEntityCategory"."entity_id" = "tCntEntity"."entity_id" AND
"tCntEntityCategory" JOIN "tCoreCategory" ON
"tCntEntityCategory"."category_id" = "tCoreCategory"."category_id" AND
"tCntEntityPerson" JOIN "tCntEntity" ON "tCntEntityPerson"."entity_id" =
"tCntEntity"."entity_id" AND
"tCntEntityPerson" JOIN "tCntPerson" ON "tCntEntityPerson"."person_id" =
"tCntPerson"."person_id" AND
"tCntContactAddress" LEFT JOIN "tCntEntity" ON
"tCntContactAddress"."entity_id" = "tCntEntity"."entity_id" AND
"tCntContactAddress" LEFT JOIN "tCntPerson" ON
"tCntContactAddress"."person_id" = "tCntPerson"."person_id" AND
"tCntContactAddress" LEFT JOIN "tCntAddress" ON
"tCntContactAddress"."address_id" = "tCntAddress"."address_id" AND
"tCoreCategory"."category" LIKE 'Internal -%';



On Fri, Jan 1, 2016 at 7:08 AM, Don Parris <parrisdc@gmail.com> 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):
>
> 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.



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