[Solved] Converting 1:n to m:n relationships

Creating tables and queries
Post Reply
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

[Solved] Converting 1:n to m:n relationships

Post by AndrewRV »

Hi guys,
This is continuation of my other thread, but since the question is completely different I decided to create a new post (I apologize for it in advance, but I couldn't find anything on the topic in the manuals)

In attached example database I have a table of movies (I use it as personal log), and another table of genres, which are related as 1:m. In the other thread I was advised to use m:n relation so I could relate as many genres to any movie as I want.
It seems more rational, but my real table have more than 1000 records in the movies table.

Is there a way to parse this database to automatically populate a new table with ID's to create a m:n relation? I'm very curious as to how this can be done.
Attachments
MoviesExample.odb
(14.64 KiB) Downloaded 130 times
Last edited by AndrewRV on Wed Oct 28, 2015 3:55 pm, edited 1 time in total.
OpenOffice 4.1.1 on Windows Server 2012 R2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting 1:n to m:n relationships

Post by Villeroy »

menu:Tools>SQL...

Code: Select all

SELECT "MovieID","GenreID1" INTO GM FROM "MoviesCompleted" WHERE NOT "GenreID1" IS NULL;
alter table GM alter column "GenreID1" INT;
alter table GM alter column "GenreID1" RENAME TO "GenreID";
insert into GM (SELECT "MovieID","GenreID2" FROM "MoviesCompleted"WHERE NOT "GenreID2" IS NULL);
insert into GM (SELECT "MovieID","GenreID3" FROM "MoviesCompleted"WHERE NOT "GenreID3" IS NULL);
alter table GM add constraint "PK_GM" primary key("MovieID","GenreID");
alter table GM add constraint "FK_Genres" foreign key ("GenreID") references "Genre"("GenreID");
alter table GM add constraint "FK_Movies" foreign key ("MovieID") references "MoviesCompleted"("MovieID");
alter table "MoviesCompleted" drop column "GenreID1";
alter table "MoviesCompleted" drop column "GenreID2";
alter table "MoviesCompleted" drop column "GenreID3";
menu:View>RefreshTables
menu:Tools>Relations...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: Converting 1:n to m:n relationships

Post by AndrewRV »

Thanks, it works! Really appreciate.

Now I just have to implement a nice form and figure out how to form different search queries, but that should be trivial
OpenOffice 4.1.1 on Windows Server 2012 R2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Converting 1:n to m:n relationships

Post by Villeroy »

m:n forms are not trivial.
Attachments
MoviesExample.odb
(26.79 KiB) Downloaded 207 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: [Solved] Converting 1:n to m:n relationships

Post by AndrewRV »

Thanks, I was having something like that in mind. Although, it would be nice to be able to select multiple genres (for example using ctrl-select) and see only movies that relate to all selected records. Is this even possible?
OpenOffice 4.1.1 on Windows Server 2012 R2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Converting 1:n to m:n relationships

Post by Villeroy »

ALTER TABLE "Genre" ADD COLUMN CHK BOOLEAN DEFAULT FALSE;
Create a query that selects only those movies having a checked genre.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: [Solved] Converting 1:n to m:n relationships

Post by AndrewRV »

Villeroy wrote:ALTER TABLE "Genre" ADD COLUMN CHK BOOLEAN DEFAULT FALSE;
Create a query that selects only those movies having a checked genre.
Thank you, I'm really grateful to you for all the help. I think I'll stick with OOBase and will try to create more complex databases for my other needs.
OpenOffice 4.1.1 on Windows Server 2012 R2
Post Reply