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

Creating tables and queries

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

Postby AndrewRV » Wed Oct 28, 2015 2:06 pm

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 54 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
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: Converting 1:n to m:n relationships

Postby Villeroy » Wed Oct 28, 2015 2:52 pm

menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting 1:n to m:n relationships

Postby AndrewRV » Wed Oct 28, 2015 3:54 pm

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
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

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

Postby Villeroy » Wed Oct 28, 2015 8:45 pm

m:n forms are not trivial.
Attachments
MoviesExample.odb
(26.79 KiB) Downloaded 83 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby AndrewRV » Thu Oct 29, 2015 8:52 am

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
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

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

Postby Villeroy » Thu Oct 29, 2015 1:35 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby AndrewRV » Fri Oct 30, 2015 9:56 am

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
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest