[Solved] Many to Many Relationship, based on a Query

[dreamquartz]

Hi All,

I am wondering if there is way to use a Query in a Many to Many relationship instead of a table.
I have created a table called tAddress, which is the m..n table type.
tAddress has a FK (Foreign Key) for the table tAddressFull.
tAddressFull is the table that holds the complete address of a location.
However there has to be a tAddressBare table, that holds the building location, and not the Floor, or UnitNumber, or Postalcode.
The reason for this approach is that our Client wants to record information about i.e. parking, how to get to the building etcetera.
This information is repeat information for their Clients, who might occupy different floors and/or different units within the same building location.
The approarch I took was to split up the addresses, and create two tables called tAddressFull and tAddressBare.
The tNotes table stores notes about the bare addresses, stored in tAddressBare.

What I would like to do, is to be able to select an address from tAddressFull, which does include the information of the bare address under FKAddressBareID, and modify the notes related to the address.
I would like to select the address, listed in tAddressFull to use it in tAddress, which holds the m..n relationship, between company and address.

What appears to be, is that to create a m..n situation, you can only use a table in the form, and no query.
I created a very simple form, called fCompanyAddress&Notes, where I am trying to figure out how to solve my issue.
If you look at the content of the form, you will find a form called fCompany (to select the Company from tCompany),
and a subform, called fAddress. This form can only use the tAddress table, instead of a query.
This form does not allow to push FKAddressBare, which I need to access tNotes.
If I change the subform to a query, I am unable to add addresses to the m..n relationship with a company.

Any suggestions are really appreciated,

Dream

Edit: Did update the attached file to latest version.
OOPS: Did upload the wrong file. Got really bored; for those who downloaded it, please ignore.

[F3K Total]

Hi,
just for your Information. A query gives a writable resultset, if all primary keys of all joined tables are selected.

[dreamquartz]

Hi,
just for your Information. A query gives a writable resultset, if all primary keys of all joined tables are selected.

Hi F3K,

Can you eleborate a little more?

I have a PK in tAddress, called AddressID. This table is the n..m relation table. In my situation the n..m realation is applicable to a company and a person on one side and an address on the other side. Therefore the tAddress holds FKAddressFullID, FKPersonID, and FKCompanyID.
The n..m relation for a person is the same principle as for a company. I did not include the tPerson table in my example.
The reason for the n..m relation for a person is that a client of my Client can also be a person, and that multiple persons can live on the same address.

Dream

[Villeroy]

In my situation the n..m realation is applicable to a company and a person on one side

This is not a relation at all. You may have addresses related to one dummy person <unknown> or relations to persons with some <unknown> dummy company but a relation to either one or the other attribute is formally wrong. For instance, you could define relations to persons where each person belongs to one company or company 'Private'.
Your database is inconsistent because you did not define any relations on database level. For instance, tAddressFull has no tAddressFullID 19 which occurs in tAdress.FKtAddressFullID.

[dreamquartz]

In my situation the n..m realation is applicable to a company and a person on one side

This is not a relation at all. You may have addresses related to one dummy person <unknown> or relations to persons with some <unknown> dummy company but a relation to either one or the other attribute is formally wrong. For instance, you could define relations to persons where each person belongs to one company or company 'Private'.
Your database is inconsistent because you did not define any relations on database level. For instance, tAddressFull has no tAddressFullID 19 which occurs in tAdress.FKtAddressFullID.

Hi Villeroy,

Updated the info.

I am trying to comply with the Client's requests.
In this case our Client indicated that there should be only one address table. However that does not work, because of their requirement to be able to add notes to an address.
The Client wants to be able to store information about the location, like i.e. the parking facilities for a building location (see tAddressBare).
The information about the floor and unitnumber is stored in an other table, called tAddressFull.
tAddressFull is used to select an address to be combined in tAddress (n..m principle) with either a company or a person.
As far as I understand it should not be a problem to set it up this way.

A bare address (i.e. the multi story office tower) can be occupied by different clients of our Client, located in different units, spread over different floors.
A client can have multiple address locations.
The principle of a company-address relation is also applicable to a person-address relation.
Our Client can also have single persons as a client.
That is why I created tAddress.

What I would like to accomplish is selecting/adding a company in tCompany via a textbox, selecting an address from tAddressFull via a listbox, and adding notes in tNotes about the tAddressBare part of the address, which is stored in tAddressFull.

The problem I am facing, is that the notes are only directly linked to AddressBareID via FKAddressBareID, and not to AddressFullID, what would be the way.

I hope the example db I provided, will help to explain what I am after.

I will go over the changed files again, to see what happens.

Dream

[MTP]

I'm looking at the form "fCompanyAddress&Notes16-08-2014@12:16PM"
If I understand correctly, what you want is to make the "Notes" table on the right hand side of the form editable?

If so, you need to change the SQL statement for the subform "fNotes". Your current SQL statement is:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID" 
	FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress" 
	WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID" 
		AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID" 
		AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

As F3K indicated, in order to be writable the query must contain the primary keys of all the tables in the FROM clause. So you would change it to:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID", "tAddressBare"."AddressBareID", "tAddressFull"."AddressFullID", "tAddress"."AddressID" 
	FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress" 
	WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID" 
		AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID" 
		AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

[dreamquartz]

I'm looking at the form "fCompanyAddress&Notes16-08-2014@12:16PM"
If I understand correctly, what you want is to make the "Notes" table on the right hand side of the form editable?

If so, you need to change the SQL statement for the subform "fNotes". Your current SQL statement is:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID" 
	FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress" 
	WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID" 
		AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID" 
		AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

As F3K indicated, in order to be writable the query must contain the primary keys of all the tables in the FROM clause. So you would change it to:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID", "tAddressBare"."AddressBareID", "tAddressFull"."AddressFullID", "tAddress"."AddressID" 
	FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress" 
	WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID" 
		AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID" 
		AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

Thanks MTP, but this is not what completely what I am looking for.
As you indicated, the right part of the form represents really what I would like to be able to do:
1. Select a Company
2. Add/Select an Address in the Table Control table below. If an address is added (selected from the tAddressFull table), it should be added to the tAddress table as n..m relation to the selected company.
3. Add/Modify notes, related to the added/selected address.

I am figthing this issue for almost a month now.

Turns out to be quite a task.

Dream

[MTP]

Your form seems like it works for 1 (clicking on the company name then using the main navigation bar - you could add a navigation bar specific to the fCompany form if your users wanted), and also for 2 (when I first tried this I got an error message about inserting a null into a non-nullable field of AddressID - looks like AutoValue wasn't turned on; once AutoValue is set to "yes" this works fine).

For 3 there are two problems. First, you don't have matching fields to link between the form fAddress (just the tAddress table) and its subform fNotes (the SQL noted in posts above). For viewing and editing existing notes fields it would be fine to link AddressFullID and FKAddressFullID (per the SQL I suggested above, AddressFullID would be available for linking). However, to add notes to addresses that don't have existing notes, the linked fields have to be the primary key for AddressBare, since that is the field in the tNotes table that needs to be autofilled by the form/subform link. To fix this, change the "content type" for fAddress to "SQL command" and use this SQL (again, making sure all primary keys from all tables are included so the query is writable):

Code: Select all

SELECT "tAddress".*, "tAddressFull"."FKAddressBareID", "tAddressFull"."AddressFullID"
	FROM "tAddress" 
	INNER JOIN "tAddressFull" ON "tAddress"."FKAddressFullID" = "tAddressFull"."AddressFullID"

The second problem: I was not able to get the subform to record FKAddressFullID from the master form as long as the subform had "content type" of "SQL command". I changed the "content type" to "Table" and set up fNotes with the table tNotes. (I also set "Entry Required" to "Yes" on "FKAddressFullID" on the table tNotes to help me debug - I'm not sure if this is needed for the form/subform link to work.)

(As an aside, I had OpenOffice crash four times while working on this, not normal for me - are you having crashing issues with this file as well?)

Does all that make sense? How does it work for you?

[dreamquartz]

Your form seems like it works for 1 (clicking on the company name then using the main navigation bar - you could add a navigation bar specific to the fCompany form if your users wanted), and also for 2 (when I first tried this I got an error message about inserting a null into a non-nullable field of AddressID - looks like AutoValue wasn't turned on; once AutoValue is set to "yes" this works fine).

For 3 there are two problems. First, you don't have matching fields to link between the form fAddress (just the tAddress table) and its subform fNotes (the SQL noted in posts above). For viewing and editing existing notes fields it would be fine to link AddressFullID and FKAddressFullID (per the SQL I suggested above, AddressFullID would be available for linking). However, to add notes to addresses that don't have existing notes, the linked fields have to be the primary key for AddressBare, since that is the field in the tNotes table that needs to be autofilled by the form/subform link. To fix this, change the "content type" for fAddress to "SQL command" and use this SQL (again, making sure all primary keys from all tables are included so the query is writable):

Code: Select all

SELECT "tAddress".*, "tAddressFull"."FKAddressBareID", "tAddressFull"."AddressFullID"
	FROM "tAddress" 
	INNER JOIN "tAddressFull" ON "tAddress"."FKAddressFullID" = "tAddressFull"."AddressFullID"

The second problem: I was not able to get the subform to record FKAddressFullID from the master form as long as the subform had "content type" of "SQL command". I changed the "content type" to "Table" and set up fNotes with the table tNotes. (I also set "Entry Required" to "Yes" on "FKAddressFullID" on the table tNotes to help me debug - I'm not sure if this is needed for the form/subform link to work.)

(As an aside, I had OpenOffice crash four times while working on this, not normal for me - are you having crashing issues with this file as well?)

Does all that make sense? How does it work for you?

Hi MTP,

It still does not work.
The moment I start, it will show the notes for the 1st entry, but going to the next one, it still shows the same note or it does not show anything, if I did not go into the Notes field.

I tried to change fAddress from showing tAddressFull.FKAddressBareID to tAddressBare.AddressBareID, added tCompany.CompanyID (both because of F3K Total and you mentioning all PK's), but that does not change anything as well.

The link between tAddress, tAddressFull, and tAddressBare, is that tAddressBare is used to build up the full address, recorded in tAddressFull. The challenge for me is that I can not link the PK AddressBareID with the PK AddressID, other than via Company.
See schematic.

Looking at it, it appears to be simple, but it turns out to be a little more than that.
There is an obvious link, as shown.

I don't have any issues with crashes, other than the odd one as the result of the 1.8 version I use to develop.
I make backups on a very regular basis, and have a history of build, consisting of more than 400 iterations.
I even installed OO4.1.0 on my Ubuntu 14.04LTS, but it does not change anything as well.

Dream

[MTP]

The moment I start, it will show the notes for the 1st entry, but going to the next one, it still shows the same note or it does not show anything, if I did not go into the Notes field.

That is very strange, it works for me to display notes (for any company/address combination I select), edit existing, and add new. In the document attached to my previous post, in the form "fCompanyAddress&Notes16-08-2014@12:16PM", the right-hand half of the form - that is the part you are looking at that doesn't work for you?

[dreamquartz]

The moment I start, it will show the notes for the 1st entry, but going to the next one, it still shows the same note or it does not show anything, if I did not go into the Notes field.

That is very strange, it works for me to display notes (for any company/address combination I select), edit existing, and add new. In the document attached to my previous post, in the form "fCompanyAddress&Notes16-08-2014@12:16PM", the right-hand half of the form - that is the part you are looking at that doesn't work for you?

Strange indeed.
I did do the following, under Ubuntu 14.04 LTS:
1. Downloaded your example, and for every new step did that again.
2. Opened it in LO 4.3; no success
3. Opened it in OO 4.1; success
4. Opened it in LO 4.3.0; no success
5. Downgraded LO to 4.2.5; prelimary success
6. Downloaded and installed LO 4.3.1; FAILED
7. Downloaded and installed LO 4.2.6; success

It appears to be a flaw in LO 4.3.

Now I am going to try to understand what you and F3K Total suggested.
Update: please see also my response to F3K Total.

Dream

[dreamquartz]

Hi,
just for your Information. A query gives a writable resultset, if all primary keys of all joined tables are selected.

Hi F3K Total,

I do not quite understand how this one played out.
MTP and you were able to help me, but I tried different approaches as well, based on your statement to try yo understand what you meant.
1. I changed the fAddress form Query by removing tAddressFull.FKAddressBareID to as a selection to tAddressBare.AddressBareID, after adding the table tAddressBare and the link between tAddressFull.FKAddressBareID - tAddressBare.AddressBareID.
As a result of your remark, this would be appropriate, but it does not work.

2. I added all the PKs of the joined tAddress, being tCompany.CompanyID and tPerson.PersonID. This does not work as well.
3. If the tAddress.FKCompanyID and the tAddress.FKPersonID are added to the selection, it does work still.

Do I have to conclude that only the referenced PKs (the FKs in my case) in the selected table need to be selected, to end up with a writable resultset of a Query?

Please advice,

Dream

[MTP]

I don't follow what you were trying with the source statement for the fAddress form. The rule about needing primary keys to get a writable result set (as opposed to a read-only result set) is only about primary keys. It has nothing to do with foreign keys. The rule is just that, for every table after FROM, that table's primary key has to be included after the SELECT. So, let's take for example this statement:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID"
   FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress"
   WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID"
      AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID"
      AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

After the FROM, there are four tables listed:
tNotes
tAddressBare
tAddressFull
tAddress

After the SELECT statement, only one of the fields is a primary key (tNotes.NoteID). For three of the four tables, no primary key is included after the SELECT. Therefore, this query returns a read-only result set.


If someone wanted to use this same query, but needed it to be writable, the primary keys for the other three tables would need to be added (tAddressBare.AddressBareID, tAddressFull.AddressFullID, tAddress.AddressID). It would look like this:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID", "tAddressBare"."AddressBareID", "tAddressFull"."AddressFullID", "tAddress"."AddressID"
   FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress"
   WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID"
      AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID"
      AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

As I said, I'm not really following your last post, so I'm not sure if this helps. Let us know?

[dreamquartz]

I don't follow what you were trying with the source statement for the fAddress form. The rule about needing primary keys to get a writable result set (as opposed to a read-only result set) is only about primary keys. It has nothing to do with foreign keys. The rule is just that, for every table after FROM, that table's primary key has to be included after the SELECT. So, let's take for example this statement:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID"
   FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress"
   WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID"
      AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID"
      AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

After the FROM, there are four tables listed:
tNotes
tAddressBare
tAddressFull
tAddress

After the SELECT statement, only one of the fields is a primary key (tNotes.NoteID). For three of the four tables, no primary key is included after the SELECT. Therefore, this query returns a read-only result set.

If someone wanted to use this same query, but needed it to be writable, the primary keys for the other three tables would need to be added (tAddressBare.AddressBareID, tAddressFull.AddressFullID, tAddress.AddressID). It would look like this:

Code: Select all

SELECT "tNotes"."Notes", "tNotes"."NotesID", "tAddressFull"."FKAddressBareID", "tAddressBare"."AddressBareID", "tAddressFull"."AddressFullID", "tAddress"."AddressID"
   FROM "tNotes", "tAddressBare", "tAddressFull", "tAddress"
   WHERE "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID"
      AND "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID"
      AND "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

As I said, I'm not really following your last post, so I'm not sure if this helps. Let us know?

Hi MTP,

It makes sense what you are saying, but I am missing something.
Your code does not allow me to connect to tNotes.NotesID.
Looking at the attachment, the Notes come via the Company, and their Address.

I tried to enter it as is, but modified it to

Code: Select all

SELECT
	 "tAddress".*,
	 "tAddressFull"."AddressFullID",
	 "tAddressFull"."FKAddressBareID",
	 "tNotes"."Notes",
	 "tNotes"."NotesID"
 FROM
	 "tNotes",
	 "tAddressBare",
	 "tAddressFull",
	 "tAddress"
 WHERE
	 "tNotes"."FKAddressBareID" = "tAddressBare"."AddressBareID" AND
	 "tAddressBare"."AddressBareID" = "tAddressFull"."FKAddressBareID" AND
	 "tAddressFull"."AddressFullID" = "tAddress"."FKAddressFullID"

That does work.

What I did, works too.
This is what I did:

Code: Select all

SELECT
	 "tAddress".*,
	 "tAddressFull"."FKAddressBareID",
	 "tAddressFull"."AddressFullID"
 FROM
	 "tAddress",
	 "tAddressFull"
 WHERE
	 "tAddress"."FKAddressFullID" = "tAddressFull"."AddressFullID"

to be able to select the Company specific Address.
For the Notes, related to the tAddressBare table, I used the table tNotes and as master: "FKAddressBareID" and slave "FKAddressBareID".

I do not see why the method I used, works as well.

There is something else, I do not understand.
Why do I have to use: "tAddressFull"."FKAddressBareID" instead of "tAddressBare"."AddressBareID". The last one is the PK of the joined table, while the first is the FK of the joined table.

F3K Total and you are indicating that all of the PKs of all the joined tables must be selected.
In your last example you select both "tAddressFull"."FKAddressBareID" and "tAddressBare"."AddressBareID". I can remove "tAddressBare"."AddressBareID", but not "tAddressFull"."FKAddressBareID", to get to the same result.

Now I do not know it anymore

Dream

[MTP]

Your code does not allow me to connect to tNotes.NotesID.

I wasn't trying to connect anything to anything. I think you were looking at the form and how the form works? I wasn't talking about the form at all, just giving an example of a query and how to make a writable (vs read-only) result set.

As far as the "what you did" example, that follows the rule of "include all PKs": after the FROM, there are two tables (tAddress and tAddressFull), and after the SELECT, you included the PKs of both tables (tAddress.* and tAddressFull.AddressFullID).

Getting the form to work requires multiple things, including selecting queries and/or tables with matching values for the form and subform, setting up the linked fields relationship, and also ensuring any query resultsets are writable. You don't describe the behavior you interpreting as "not working", so I'm not sure if the need for the foreign key in the query has anything to do with a writable resultset (which would be a bug, and is possible, Base is much buggier than the other OpenOffice products) or if it is instead needed for the form/subform relationship to work (which is a whole different issue).

[dreamquartz]

The interesting thing is that you mention all the PK's, but as I said, the FK "tAddressFull"."FKAddressBareID" (as you used in your example) can not be replaced by PK "tAddressBare"."AddressBareID". I added the table "tAddressBare" and added the link between the 2. I removed the FK, and replaced it by the PK. I thought it would be the same, and in line with your suggestion.
It simply shows Notes, but they are not linked to a selected Address.
The notes do not change with the change of Company and/or selection of related Address.

Apparantly I am thinking/doing something here, what I should not be doing, and am wondering why.

Dream