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 <parri...@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+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
