Ask Your Question

How to change type relation in LibreOffice Base 5.3 or 5.2.5?

asked 2017-02-11 21:47:58 +0200

Pyroxar gravatar image

I create relation between tables and I don't know how to change type relation.

Can you do relation like as film? I do not.

How to reverse direction relations?

edit retag flag offensive close merge delete


In the LO relationship windows only connections are setup up, without regard to how the connection will be used. Later in a query or view you can clarify a relationship between tables to be either, full join, inner join, left join or right join, depending on what your needs are. (Note, this is different from how the MS Access Relationships window works. In access join type hints are provided in the relationships window to later help setup the default query relationships.)

EasyTrieve gravatar imageEasyTrieve ( 2017-02-12 02:24:08 +0200 )edit

I have relation many to one but I want have relation one to many.

Pyroxar gravatar imagePyroxar ( 2017-02-12 12:25:44 +0200 )edit

@Pyroxar, I appreciate you asking here, and your question suggests you are confused by the first principles of relational database design. I suggest you buy a good book that explains the first principles behind and beneath relational databases. This stuff has been around for 40 years that I know of, and there are good textbooks on the subject that address first principles. (My handle here, EasyTrieve, is the name of the first database front end I ever used in the late 70s on a mainframe.) :-)

EasyTrieve gravatar imageEasyTrieve ( 2017-02-22 23:15:45 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-02-11 22:30:42 +0200

Ratslinger gravatar image

updated 2017-02-12 15:28:51 +0200

The video is a very basic set of relations. For more information see Chapter 3 - Tables in the LibreOffice basic guide found at this link. There are also samples you can download.

Relations are always from one table to another. "How to reverse direction relations?" does not make sense.

Also, if you want to edit or delete the relation, right mouse click on the relationship line.

There is a bit more information in the off-line help file (F1) if you have installed it.

Edit 2/12/17:

The relation "Type" cannot be picked from a list. It depends upon the field and what it can contain. A Primary Key field or a field marked as Unique must contain data in each record which is different from every other record in that table. A different field where you may be storing the "City" for an address would not be marked as such since the same city can be in many different records of the table.

Therefore, when creating a relation from a Primary Key or Unique field (type = "One") to another field such as the field containing "City" above (type = "Many"), you have created a One-To-Many relationship. As you can see, this cannot be changed because of the field restrictions.

edit flag offensive delete link more


Will edit my answer because of space requirements.

Ratslinger gravatar imageRatslinger ( 2017-02-12 15:12:57 +0200 )edit

answered 2017-02-13 01:55:09 +0200

EasyTrieve gravatar image

In the query, view or similar design view right click on line connecting the two tables and select edit.

image description

Change the Type form Left join to a Right join (or vise versa), OR do likewise in the underlying SQL. Or you can also delete the connection and drag the cursor from one table to the other to and then again edit this. If you drag from left to right you'll get one pair of connected fields, or if right ot left, the opposite pair of fields.

image description

I've given you some Karma so you can vote and better participate here.

If my answer helped you, vote it with ✔ and with ∧ (on the left)

edit flag offensive delete link more


Curious. How does this answer a One-to-Many or Many-to-One relationship change?

Ratslinger gravatar imageRatslinger ( 2017-02-13 02:02:33 +0200 )edit

@Ratslinger, I imagine the questioner is confused. Obviously if you have a primary/unique key that will always be the one. But from his initial question, " I don't know how to change type relation." I'm guessing Left Join, "Contains ALL record from table 'Products' but only records from table 'Companies' where the values in the related fields are matching." Right Join, "Contains ALL records from table 'Companies' but only records from table 'Products' where the values in the related fields ...

EasyTrieve gravatar imageEasyTrieve ( 2017-02-13 02:30:17 +0200 )edit

@EasyTrieve you missed an important part of the question - the linked video showed exactly what was being referred to.

Ratslinger gravatar imageRatslinger ( 2017-02-13 02:36:46 +0200 )edit

@Ratslinger, Sorry, but I fail to see what you are getting at. I had already watched the you-tube video before I wrote my answer and I just watched it again. It shows 3 conventional tables, each with a primary key, linked in a very conventional way. You can't reverse the one to many relationship between these tables, not unless you first change the underlying tables themselves, and especially how the keys are set up. I didn't see a question about table design.

EasyTrieve gravatar imageEasyTrieve ( 2017-02-13 02:48:38 +0200 )edit

But that is what the question is about. You are saying to yourself the person asking the question has this knowledge. There are many who are new to this and don't yet have some of the basics and need some help/direction.

Ratslinger gravatar imageRatslinger ( 2017-02-13 02:57:45 +0200 )edit

I agree that the question is a basic question, and not an advanced question. But it was not a question about what a relation is or how to set one up. I think that question could be much better answered by some other web page that introduces databases and relations. The question was about how to get LO to do something. And even you wrote that the question "does not make sense". I think saying what does easily work is the best answer at this point, rather than suggesting further complexity

EasyTrieve gravatar imageEasyTrieve ( 2017-02-13 04:53:00 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-02-11 21:47:58 +0200

Seen: 261 times

Last updated: Feb 13 '17