Hi Everybody, New to the Forum and LibreOffice. I am attempting to make a simple music database and have followed many of the posts and documentation, but still struggling. I have attached Test.odb - Google Drive a sample database, with Artist ID, Artist, Song ID, Song Titles, as well as a Junction Table that has the relationship for the Artist ID and The Song ID. My problem is I’m trying to automatically fill the info in the Junction Table without having to manually type more than a thousand entries. I have run a query and this sample database seems to work as I would like, but again that was manually entering the info in the Junction Table.
Can anyone suggest a solution to this problem Please and Thank You
Hello,
With a junction table (many-to-many relation) it is necessary who/what belongs where. What artist recorded what song or what song was recorded by whom.
Based upon the sample I cannot see how this is possible with the presented information. For example, there is no way of knowing who the artist is for Honey, Honey. Because of that, there is no means to create an entry in the junction table.
You do mention something about a query but I see nothing of this in the sample or your question.
Also, and as an aside, it is always helpful to note some basic information such as specific LO version, OS and database used (especially when dealing with SQL).
Firstly, Thank You for your reply. The information provided shows that my thought process here is not clear, and my inexperience with Base basics. I had hoped that by having an Artist Table with Artist ID and a Song Title Table with Song ID and Title, by placing the ID’s in a Junction Table this would match up the information. I thought by having this relationship in the Junction Table the ID’s from both Tables would autofill into the Junction Table. I ran a query (which I didn’t save) and the result came out as I had hoped, Artist ID, Artist, Song ID, Song Title. This because I manually typed the ID’s into the Junction Table. In the sample provided I only input info for the first 5 songs which belong to 10CC. Please excuse the Rookie error about the basics Version: 7.1.5.2 (x64) / LibreOffice Community
Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5
CPU threads: 12; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-US
Calc: threaded
If you already have an external table, wich contains Artist AND Song this can made usable and converted in a junction table with ids.
If your table with the songs is ordered by some criteria it may be possible to cheat a bit.
So, if you know the songs from Id 200 to 250 are from ABBA, it may be possible to set the artist-ids as a group.
Depends on the task you actually want to do…
Thanks for your reply I did make an earlier Table which has a seperate ID attributed to each artist and song. For instance ABBA may have 10 songs and the records show 0-9 all ABBA with all the song Titles. I couldn’t work out how to have ID 0, ABBA, song Titles 0-9 if that makes sense.
Do you still have that table? If so, please provide its format and possibly a sample.
Yes Ratslinger I still Have the Table mentioned above. https://drive.google.com/file/d/1rClziDAGD0MyqnhZao3VVS0NsAFdSK1j/view?usp=sharing
Is there a way to upload Sample Files straight to a post?
Version: 7.1.5.2 (x64) / LibreOffice Community
Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5
CPU threads: 12; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-US
Calc: threaded
When posting there is a toolbar at the top of the post. There is an icon for Upload. See → This is the guide - How to use the Ask site?
Will have a look at your file.
Edit:
Quick look at file only shows 27 entries but your original post has over one thousand noted. Is this only a partial file?
Yes this is just a sample. The original has about 2,000 files
My test appears OK. Make sure you try this with a backup on hand (or two 
).
Using last sample and 70sMusic table, I creates tables for “Artist” and “Song”. Then used SQL to fill tables ( using Tools->SQL ).
Then used your “Junction” table from original post and another SQL statement to put the three tables together to be able to extract the ID fields for the junction table.
Saved the SQL Insert statements in Queries (cannot run from there). They are:
- ArtistInsert
- SongInsert
- JunctionInsert
Junction being done last. Again, do these from Tools->SQL.
70sMusic.odb (6.7 KB)
Thank You so much Ratslinger I sure do appreciate your helpful efforts. I now need to investigate with your sample how to make the form with the information I require. I’ll continue to play around with this and try to get the form design I require. Thanks again for your time and help.
Hi Ratslinger, Sorry for the bother. I tested your sample and everything worked as suggested. With confidence I decided to try your queries on the total database, and a few strange things happened.
The 70sMusic table is the same only with the full amount of songs. I copied the Junction Table with the Artist_ID and Song_ID. Created the Tables for Artist & Songs and all looked good. Copied the Queries into the query area copied the SQL queries and and ran them in the order you suggested. The Artist Query said executed successfully and the output showed all the Artists. The same for the Song Query executed successfully and the output showed all Songs. Did the same with the Junction Insert executed successfully, but 0 updated in the output. None of the Tables were updated.
I may have missed something but pretty sure I did as suggested. There were two extra queries not mentioned DistinctArtist (which appeared to be the same as ArtistInsert and the TestJoins wasn’t sure if I should run them? I did end up running Test Joins and it came up with this error message [1: Table not found in statement [SELECT “SID”, “AID” FROM ( SELECT “Song”.“ID” “SID”, “Song_Title”, “70sMusic”.“Artist”, “Artist”.“ID” “AID” FROM “Song”]
Hopefully you will know what the issue is. Many Thanks
Did you delete the existing records that were in it? Did you give it a different name - should be Junction.
My test again shows no problem:
I also ran the TestJoins query without issue. That and DistinctArtist were my testing leading up to the end product.
I don’t know what else I can provide unless you post your actual files which are problematic.
@Ringy9
I downloaded your database “70sMusic” and produced a solution.
I have not downloaded any of @Ratslingers uploads but as you have encountered issues I will make my solution available.
my solution uses a single query which is executed via menu:Tools>SQL.
your table “70sMusic” is the data source for 3 tables “tArtist”, “tSongs” and “tArtist_Song”.
--must be executed from menu:Tools>SQL
--creates 3 individual tables from the single table "70sMusic"
drop table "tArtist_Song" if exists;
drop table "tArtist" if exists;
drop table "tSongs" if exists;
select id, "Artist" into "tArtist"
from
(
select count(m2."Artist") id, m1."Artist"
from
(select distinct "Artist" from "70sMusic") m1
left join
(select distinct "Artist" from "70sMusic") m2
on m1."Artist" >= m2."Artist"
group by m1."Artist"
);
alter table "tArtist" alter id set not null;
alter table "tArtist" add primary key(id);
alter table "tArtist" alter id identity;
alter table "tArtist" alter "Artist" varchar(100) not null;
select id, "Song_Title" into "tSongs"
from
(select id, "Song_Title" from "70sMusic");
alter table "tSongs" add primary key(id);
alter table "tSongs" alter id identity;
alter table "tSongs" alter "Song_Title" varchar(150) not null;
select a.id "ArtistID", b.id "SongID" into "tArtist_Song"
from (select * from "tArtist") a
join (select id, "Artist" from "70sMusic") b on a."Artist" = b. "Artist";
alter table "tArtist_Song" add primary key("ArtistID", "SongID");
alter table "tArtist_Song" add foreign key ("ArtistID") references "tArtist" (id) on delete cascade on update cascade;
alter table "tArtist_Song" add foreign key ("SongID") references "tSongs" (id) on delete cascade on update cascade;
A simple query to display artist and song:
–shows all artists and their songs
select a2."Artist", s."Song_Title" from "tArtist_Song" a1 join "tArtist" a2 on a1."ArtistID" = a2.ID join "tSongs" s on a1."SongID" = s.ID order by "Artist", "Song_Title"
1 Like
Hi Ratslinger I will upload the full database as I can’t seem to get it to work. The Only thing I have noticed is that the SongInsert query appears to be Cut Off.(the word SELECT appears as CT) I tried to update to the complete word SELECT and save but it just goes back to CT. If you can please find the time to investigate further I would be truly grateful.
70’s_Music.odb (107.0 KB)
You have multiple issues. For one ID’s in tables were not auto increment. There were other issues and did not bother with all. Instead of taking time to resolve, use the SQL from @cpb above. My quick test shows that works.
Thank you for your Kindness. I will do as suggested. Your input has been invaluable.
Thank you cpb for your input. Your solution has worked for me and is very helpful
@Ringy9,
Please NOTE:
I created a table of unique artists.
if 2 artists perform the same song then song ID is unique but “Song_Title” is duplicated because I took the IDs from your table “70sMuxsic”.
song titles should not be duplicated so please use the code below, it’s been modified and “Song_Title” is now unique which means that even if multiple performers sing the same song it will only appear once in the table “tSong”.
I have a CD database which contains 808 tracks of which 5 have duplicate titles.
there are 3 tables “tArtist”, “tAlbum” and “tTracks”. “tAlbum” has the foreign key “ArtistID” and “tTracks” has the foreign key “AlbumID”. input is simple using 3 table controls.
sometimes it’s ok to bend the rules! (a handful of duplicates).
if I used a junction table to link each artist with one of 803 songs then inputting fresh data would be very painful because I would first need to check if the title exists and if not then insert a new record.
a junction table may not be the most practical option, it’s just a thought.
--must be executed from menu:Tools>SQL
--creates 3 individual tables from the single table "70sMusic"
--artists and songs are both unique i.e. no duplicates in tables "tArtist" or "tSongs"
drop table "tArtist_Song" if exists;
drop table "tArtist" if exists;
drop table "tSongs" if exists;
select count(m2."Artist") id, m1."Artist" into "tArtist"
from
(select distinct "Artist" from "70sMusic") m1
join
(select distinct "Artist" from "70sMusic") m2
on m1."Artist" >= m2."Artist"
group by m1."Artist"
;
alter table "tArtist" alter id set not null;
alter table "tArtist" add primary key(id);
alter table "tArtist" alter id identity;
alter table "tArtist" alter "Artist" varchar(100) not null;
select count(s2."Song_Title") id, s1."Song_Title" into "tSongs"
from
(select distinct "Song_Title" from "70sMusic") s1
join
(select distinct "Song_Title" from "70sMusic") s2
on s1."Song_Title" >= s2."Song_Title"
group by s1."Song_Title"
;
alter table "tSongs" add primary key(id);
alter table "tSongs" alter id identity;
alter table "tSongs" alter "Song_Title" varchar(150) not null;
select distinct a.id "ArtistID", s.id "SongID" into "tArtist_Song"
from
(select "Artist", "Song_Title" from "70sMusic") m
join "tArtist" a on m."Artist" = a."Artist"
join "tSongs" s on m."Song_Title" = s."Song_Title"
;
alter table "tArtist_Song" add primary key("ArtistID", "SongID");
alter table "tArtist_Song" add foreign key ("ArtistID") references "tArtist" (id) on delete cascade on update cascade;
alter table "tArtist_Song" add foreign key ("SongID") references "tSongs" (id) on delete cascade on update cascade;Thank You again so much. This database has nearly 2000 70’s songs and I’m adding all the time, some of which are as you say duplicate song titles. I am just starting to work on the forms side of things, and I need to figure out how to make additions to the database in an easier manner. If you haven’t guessed already this is my first venture in creating a database and it’s turning out quite a large learning curve