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