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.
[Solved] Converting 1:n to m:n relationships
[Solved] Converting 1:n to m:n relationships
- 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
Re: Converting 1:n to m:n relationships
menu:Tools>SQL...
menu:View>RefreshTables
menu:Tools>Relations...
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: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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Converting 1:n to m:n relationships
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
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
Re: [Solved] Converting 1:n to m:n relationships
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Converting 1:n to m:n relationships
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
Re: [Solved] Converting 1:n to m:n relationships
ALTER TABLE "Genre" ADD COLUMN CHK BOOLEAN DEFAULT FALSE;
Create a query that selects only those movies having a checked genre.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Converting 1:n to m:n relationships
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.Villeroy wrote:ALTER TABLE "Genre" ADD COLUMN CHK BOOLEAN DEFAULT FALSE;
Create a query that selects only those movies having a checked genre.
OpenOffice 4.1.1 on Windows Server 2012 R2