LOBase Query - Left or Right Join?

classic Classic list List threaded Threaded
13 messages Options
Don Parris Don Parris
Reply | Threaded
Open this post in threaded view
|

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: [hidden email]
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
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?



On 01-01-16 13:08, Don Parris 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):
>
>


In design mode, right-click on the link between two tables, and click
'edit'...


If you want to manually edit the SQL, the syntax is shown here:
https://help.libreoffice.org/Common/Special_Settings#Use_Outer_Join_syntax_.27.7BOJ_.7D.27


--
To unsubscribe e-mail to: [hidden email]
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
Windows10 / LibreOffice  (latest?)
Don Parris Don Parris
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

On Fri, Jan 1, 2016 at 10:59 AM, Luuk <[hidden email]> wrote:

>
>
>
> <SNIP>
>>
>>
>>
>
> In design mode, right-click on the link between two tables, and click
> 'edit'...
>
>
> If you want to manually edit the SQL, the syntax is shown here:
>
> https://help.libreoffice.org/Common/Special_Settings#Use_Outer_Join_syntax_.27.7BOJ_.7D.27
>
>
>

Thanks, but neither of these is very helpful.  Editing the relationships
only allows me to set the CASCADE and DELETE options (unless I am missing
something, in which case maybe a little further guidance might help.)

Following the link, I tried to set the special settings, but don't have any
option as suggested - using LO 4.4.7.2 (standard LOBase internal database)
on Manjaro Linux here.

I have only 3 special settings available via the "Edit-Database-Advanced
Settings" as suggested:
<> End Text Lines with CR+LF
<> Form Data input checks for required fields
<> Use ODBC conformant date/time fields.

There is no option whatsoever to allow to use outer join syntax.

Regards,
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: [hidden email]
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
Don Parris Don Parris
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

In reply to this post by Don Parris
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 <[hidden email]> 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: [hidden email]
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
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

reply below....

On 01-01-16 18:06, Don Parris 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 -%';
>
>
>

This was the second link i gave you

it says:
Example
|select Article.* from {oj item LEFT OUTER JOIN orders ON
item.no=orders.ANR}

The link was:
https://help.libreoffice.org/Common/Special_Settings#Use_Outer_Join_syntax_.27.7BOJ_.7D.27

i do not see any '{', 'oj', '}' in your statement
|

--
To unsubscribe e-mail to: [hidden email]
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
Windows10 / LibreOffice  (latest?)
Paul Steyn Paul Steyn
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

In reply to this post by Don Parris
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 <[hidden email]> 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 <[hidden email]> 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: [hidden email]
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

Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?



On 01-01-16 22:08, Paul Steyn wrote:

> 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.
>
>
You are absolutely right, but the *syntax* is different in LO ....
see link i posted earlier

--
To unsubscribe e-mail to: [hidden email]
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

Windows10 / LibreOffice  (latest?)
Don Parris Don Parris
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

In reply to this post by Luuk
On Fri, Jan 1, 2016 at 1:18 PM, Luuk <[hidden email]> wrote:

> reply below....
>
>>
>> <SNIP>

>
> This was the second link i gave you
>
> it says:
> Example
> |select Article.* from {oj item LEFT OUTER JOIN orders ON item.no
> =orders.ANR}
>
> The link was:
>
> https://help.libreoffice.org/Common/Special_Settings#Use_Outer_Join_syntax_.27.7BOJ_.7D.27
>
> i do not see any '{', 'oj', '}' in your statement
>
>
Thanks Luuk.  I guess I misunderstood the information.  Reading the top of
the page, I got the idea that the '{', 'oj', '}' part should be "activated"
so to speak in the settings.  I realize now it is part of the SQL statement.


Regards,
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>
GPG Key ID: F5E179BE

--
To unsubscribe e-mail to: [hidden email]
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
Don Parris Don Parris
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

In reply to this post by Luuk
On Sat, Jan 2, 2016 at 4:36 AM, Luuk <[hidden email]> wrote:

>
>
> On 01-01-16 22:08, Paul Steyn wrote:
>
>> 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).
>>
>> Thanks Paul,  I'll acknowledge that my use of explicit JOIN statements is
limited - this is probably one of the most complicated queries I've done so
far.



> 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.
>>
>> I really should have formatted - sorry about that.



> 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.
>>
>
I do normally use aliases, but I really had not gotten that far with this
query - just trying to sort of get it written down.  Oops.

>
>> Thirdly, JOINs are complicated, as there are many options: INNER
>> JOINs, LEFT, RIGHT and FULL OUTER JOINs, CROSS JOINs, NATURAL JOINs,
>> etc.
>>
>>
>> I think I have a basic grasp, but not the practical implementation -
particularly on such a complicated query as this one.  I am returning to an
old project after two years of really not thinking much about the joins,
etc.



> You are absolutely right, but the *syntax* is different in LO ....
> see link i posted earlier
>
>
>
>
I have tried this query, which produces a "Column Not Found" error.  Note
that I removed the other tables (from my OP) regarding the contact info,
just to try and keep it relatively simple at the moment.  I wonder if my
placement of the '{oj}' bit might make a difference?

SELECT
"tCoreCategory"."category" "Category",
"tCntEntity"."entity_name" "Entity",
"tCntPerson"."first_name" "First Name",
"tCntPerson"."last_name" "Last Name",
"tCntAddress"."location_name" "Location",
"tCntAddress"."address1" "Address"
FROM { oj
"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" }



--
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: [hidden email]
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
Don Parris Don Parris
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

In reply to this post by Luuk
On Sat, Jan 2, 2016 at 4:36 AM, Luuk <[hidden email]> wrote:

>
>
> On 01-01-16 22:08, Paul Steyn wrote:
>
>> Hi Don,
>> <SNIP>
>
>

> You are absolutely right, but the *syntax* is different in LO ....
>>
> see link i posted earlier
>
>
>
>
> Still trying to work out the OUTER JOIN syntax apparently.  I tried a
simpler outer join statement:
SELECT
"tCoreType"."type" "Type", "tCoreCategory"."category" "Category"
FROM {oj "tCoreType" "ctype" LEFT OUTER JOIN "tCoreCategory" "ccat"
  ON "ctype"."type_id" = "ccat"."type_id"}


This gives me the following error:
SQL Status: S0022
Error code: -28

Column not found: tCoreType.type in statement [SELECT "tCoreType"."type"
"Type", "tCoreCategory"."category" "Category" FROM      "tCoreType" "ctype"
LEFT OUTER JOIN "tCoreCategory" "ccat" ON "ctype"."type_id" =
"ccat"."type_id"  ]

The column "type" is most certainly one of the columns in the specified
table.  No matter what order or how I try to enter this, it just comes up
with an error message.  Any ideas?  I really would like to learn how to
make this work.

--
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: [hidden email]
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
libreoffice-ml.mbourne libreoffice-ml.mbourne
Reply | Threaded
Open this post in threaded view
|

Re: LOBase Query - Left or Right Join?

Don Parris wrote:

> Still trying to work out the OUTER JOIN syntax apparently.  I tried a
> simpler outer join statement:
> SELECT
> "tCoreType"."type" "Type", "tCoreCategory"."category" "Category"
> FROM {oj "tCoreType" "ctype" LEFT OUTER JOIN "tCoreCategory" "ccat"
>    ON "ctype"."type_id" = "ccat"."type_id"}
>
>
> This gives me the following error:
> SQL Status: S0022
> Error code: -28
>
> Column not found: tCoreType.type in statement [SELECT "tCoreType"."type"
> "Type", "tCoreCategory"."category" "Category" FROM      "tCoreType" "ctype"
> LEFT OUTER JOIN "tCoreCategory" "ccat" ON "ctype"."type_id" =
> "ccat"."type_id"  ]
>
> The column "type" is most certainly one of the columns in the specified
> table.  No matter what order or how I try to enter this, it just comes up
> with an error message.  Any ideas?  I really would like to learn how to
> make this work.

"tCoreType" "ctype" in the FROM clause renames the "tCoreType" as
"ctype" for the purposes of this query. So in the SELECT clause you need
to refer to it as "ctype", as you have done in the join condition.
Likewise for renaming "tCoreCategory" to "ccat". So:
SELECT "ctype"."type" "Type", "ccat"."category" "Category" ...

Mark.


--
To unsubscribe e-mail to: [hidden email]
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
Walther Koehler-4 Walther Koehler-4
Reply | Threaded
Open this post in threaded view
|

Re: LO-Base - Autoreconnect

In reply to this post by Don Parris
Hello list,

me too would like to have a reconnect:

When LO-Base the first connection to a dBase
database (directory) has made, all the existing *.dbf files in that directory
are recognized and shown as tables in *.odb.

However, if you add manually or by a third party program another *.dbf, it is
not recognized until one terminates the running LO (usually with many open
writer, calc files).

Is there a way other than by a makro (getTables()) to update the tables
(=*.dbf file list) while in LO Base?

Walther

--
To unsubscribe e-mail to: [hidden email]
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
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: LO-Base - Autoreconnect

Am 27.01.2016 um 13:16 schrieb Walther Koehler:

> Hello list,
>
> me too would like to have a reconnect:
>
> When LO-Base the first connection to a dBase
> database (directory) has made, all the existing *.dbf files in that directory
> are recognized and shown as tables in *.odb.
>
> However, if you add manually or by a third party program another *.dbf, it is
> not recognized until one terminates the running LO (usually with many open
> writer, calc files).
>
> Is there a way other than by a makro (getTables()) to update the tables
> (=*.dbf file list) while in LO Base?
>
> Walther
>

menu:View>Refresh Tables


--
To unsubscribe e-mail to: [hidden email]
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