Hi Don, Your SELECT statement is indeed incorrect; it seems as though you don't fully understand joins (I could be mistaken, but your syntax is off by enough to suggest this).
Firstly, choose a format for your SELECT statement to make it easier to read. I've reformatted it below using one such formatting standard that I've used in the past, but you can of course choose your own. The important thing is that it isn't simply one large blob of text. Secondly, use table aliases. After the table name in the FROM clause, you can include a table alias, which you can use elsewhere (including in the SELECT clause) to refer to the table. These are often much shorter than the table names, making the whole statement easier to read. Also, if you are including a table more than once (for different join conditions), I think you are required to have aliases to distinguish between the two table uses. You are actually doing this in your statement, and it gives rise to an ambiguity. Thirdly, JOINs are complicated, as there are many options: INNER JOINs, LEFT, RIGHT and FULL OUTER JOINs, CROSS JOINs, NATURAL JOINs, etc. https://en.wikipedia.org//wiki/Join_(SQL) The most common (in my experience) are INNER and LEFT OUTER JOINs. Don't worry about the rest for now. Think of it this way: For inner joins, with the condition in the FROM clause (i.e. FROM tableA a INNER JOIN tableB b on a.id = b.id) You are adding all rows from tableA to a result set, then, for each row in tableB that matches the condition, you are adding all the fields from tableB to that row of the result set (if more than one row matches a row in the result set, the existing row is duplicated). If a row in the result set doesn't match any rows in tableB, it is removed from the result set. For OUTER JOINS, of the form: FROM tableA a LEFT OUTER JOIN tableB b on a.id = b.id You are doing the same thing, except that should a row in the result set not match any rows in tableB, it is not discarded from the result set, and instead NULL values are used for all fields that would otherwise have come from tableB. Now your statement has a lot of AND parts in the JOINs, which don't have proper conditions, and so they look like they may have come about due to you not understanding the syntax properly, and aren't actually needed, so I have removed them. I've also re-ordered the FROM list, which shouldn't strictly speaking be necessary (I think), but does make it easier to follow logically if tables are listed in the FROM list before other tables reference them in their JOIN conditions. You're also missing the "tCntEntityPerson" table in the FROM list, as it is used in the JOIN conditions of other tables, but as you've listed "tCntEntity" twice, with the second one having "tCntEntityPerson" in the join condition, I've assumed that this was a mistype (or misunderstanding), and I've corrected that to be the missing "tCntEntityPerson" in the FROM list. The same with "tCntContactAddress". And as you've included the "tCntPerson" table twice, joined to different tables, there is an ambiguity in your SELECT clause as to which table you are referring to. I've used aliases to clarify that, but you'll have to correct those two lines in the SEELECT statement yourself, as only you know which table you want data from. So, your SELECT statement should be *something* like the following: SELECT core_cat."category", ent."entity_name", pers_from_[ent or addr?]."first_name", pers_from_[ent or addr?]."last_name", addr."location_name", addr."address1" FROM "tCntEntityCategory" ent_cat JOIN "tCntEntity" ent ON ent_cat."entity_id" = ent."entity_id" JOIN "tCoreCategory" core_cat ON ent_cat."category_id" = core_cat."category_id" AND core_cat."category" LIKE 'Internal -%'; JOIN "tCntEntityPerson" ent_pers ON ent_pers."entity_id" = ent."entity_id" JOIN "tCntPerson" pers_from_ent ON ent_pers."person_id" = pers_from_ent."person_id" LEFT OUTER JOIN "tCntContactAddress" ctct_addr ON ctct_addr."entity_id" = ent."entity_id" LEFT OUTER JOIN "tCntPerson" pers_from_addr ON ctct_addr."person_id" = pers_from_addr."person_id" LEFT OUTER JOIN "tCntAddress" addr ON ctct_addr."address_id" = addr."address_id" (Sorry, I haven't actually tested this, just eyeballed it, so this may be syntactically or logically incorrect, but it should get you pretty close to what you need.) Hope this helps. Paul On Fri, 1 Jan 2016 12:06:56 -0500 Don Parris <[email protected]> wrote: > 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 <[email protected]> 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. > > > > > > > -- To unsubscribe e-mail to: [email protected] 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
