Quick SQL help needed

[Confused]

EDIT: I've decided to move the genre checking into the DB build/update routine. Check my last post for details


Original Post:

I'm in the process of writing an in-car media application, and i've come to displaying all the unique genres that you have within your music database.

I have a table called music, with a field called genreid, which has both the genre number (54, 36 etc), and text (ie Rock, Classical etc). I can pull in the distinct values from here and display them, but I really want to just display the text equivelents of the numerical genre id's.

I also have a table called genre which has a list of the ID3 genres id's, and their corresponding names.

Now, i'm a little stuck on how to compare the numbers from the music table to the genre table and pull out the text from the genre table, and make sure that I only display each unique text once.

I guess it's some kind of linking of the tables/searches, but my knowledge of SQL isn't enough to do this yet!


Thanks in advance!


Garry

 

[thirtythree]

If you had asked me 2 years ago, I might know.

 

[imported_vr6]

you need to do a join/query

select fieldname1, fielname2
from table1, table2
where table1.field = table2.field and
table1.field = id

something to that degree, hav't did sql for ages so syntax might be wrong.

 

[Lazy8s]

Select m.genreid, unique(m.text), g.genreid, unique(g.name)
From music m, genre g
WHERE m.genreid like g.genreid
SORT BY g.name asc;


That ought to at least get you started, without knowing the table names/fields exactly that's all the more I can give. If you can do a "desc music" and "desc genre" and post that so I can see the table names/column names and tell me exactly what to compare I can write the statement.

 

[DBL]

Something like...

Select distinct genre.name from genre, music where genre.ID = music.genreid

 

[imported_vr6]

-nevermind, someone beat me to it

 

[MrChad]

SELECT Genre_Name
FROM genre
WHERE id IN (SELECT DISTINCT genreid FROM music)

EDIT:

SELECT DISTINCT genre.Genre_Name
FROM genre, music
WHERE genre.id = music.genreid

may also work

 

[Confused]

Well, this isn't exactly plain SQL, it's DAO in VB6, but i think i can modify some of this.

Lazy8s, this is what I have:

Table: music
Fields: id, filename, artist, album, tracktitle, trackno, genreid, favourite, badtags

Table: genre
Fields: id, genre

So, I need to compare any numerical rows in music.genreid to the genre.id row and get the genre.genre field back, and make sure that I only have one value for example "Rock" in the result


Garry

 

[Mr.IncrediblyBored]

Originally posted by: Confused
Well, this isn't exactly plain SQL, it's DAO in VB6, but i think i can modify some of this.

Lazy8s, this is what I have:

Table: music
Fields: id, filename, artist, album, tracktitle, trackno, genreid, favourite, badtags

Table: genre
Fields: id, genre

So, I need to compare any numerical rows in music.genreid to the genre.id row and get the genre.genre field back, and make sure that I only have one value for example "Rock" in the result


Garry

What fields do you actually need to return in this query? Just the genre, or all of the music fields plus genre name instead of the ID? I have an idea for what you need, but the columns in the select will determine the joins.

 

[Kraeji]

truncate table music;

 

[new2AMD]

Ill be looking for the finished product on mp3car soon Garry

 

[Confused]

KnightBreed,

Actually, thinking about this, it might be easier to do this comparison when I build the database, rather than at run-time, as I am going to be using these results to filter down the list of all music into genres/artists/albums etc.


So, then, I think I can get round this by checking the genre that I read from the ID3 (and Ogg and FLAC and APE etc) tags when I read the files, and then just put the genre name into the music database rather than putting any numerical values in...


new2AMD, yeah, i've been working on it for about 6 weeks so far and it's in a state where I'm using the daily builds in my car!! All other browsing works apart from Genre now, and this is the exact reason why


Garry

 

[Mr.IncrediblyBored]

There are a number of ways you can do this. The distinct on the select shouldn't be necessary if the ID in the table genre is the primary key and you know there are no duplicates IDs.

SELECT DISTINCT music.id, music.filename, etc..., genre.genre
FROM music, genre
WHERE music.genreid *= genre.id;

Perhaps an inline column select if you wanted to get overly complicated and useless?

SELECT music.id, music.filename, etc..., (SELECT DISTINCT genre.genre FROM genre WHERE genre.id = music.genreid)
FROM music;

 

[imported_vr6]

i think most of the posts up top are valid.

I would suggest creating a genre table with two fields, genreid, genrename. It would make it alot easier. Any tables that need to reference it will only need the id, saves time and space.

Since currently the current genreID is actually number&name, then you have to do more processing each time you want to just display the name.

 

[Mr.IncrediblyBored]

Originally posted by: Kwan1
i think most of the posts up top are valid. I would suggest creating a genre table with two fields, genreid, genrename. It would make it alot easier. Any tables that need to reference it will only need the id, saves time and space.

Um, that's exactly what he did.

 

[imported_vr6]

Originally posted by: KnightBreed

Originally posted by: Kwan1
i think most of the posts up top are valid. I would suggest creating a genre table with two fields, genreid, genrename. It would make it alot easier. Any tables that need to reference it will only need the id, saves time and space.

Um, that's exactly what he did.

have a table called music, with a field called genreid, which has both the genre number (54, 36 etc), and text (ie Rock, Classical etc).

I mean in the music table, why have one field with two parts?

 

[Beau]

SELECT music.* from music
INNER JOIN genre on genre.id = music.genereid;

 

[Confused]

Originally posted by: Kwan1

Originally posted by: KnightBreed

Originally posted by: Kwan1
i think most of the posts up top are valid. I would suggest creating a genre table with two fields, genreid, genrename. It would make it alot easier. Any tables that need to reference it will only need the id, saves time and space.

Um, that's exactly what he did.

have a table called music, with a field called genreid, which has both the genre number (54, 36 etc), and text (ie Rock, Classical etc).

I mean in the music table, why have one field with two parts?

Nope, the genreid field in the music table contains either the id, OR the text, depending on what is read in from the tags in the media files (some, such as ID3, store only an integer value (id), some, such as Ogg, store the name, not the id), so my genreid field contains both numbers and text, depending on which type of file format it was read from (ID3, Ogg etc)


I've decided that as I'm going to be using the genre in more places, I will just compare it when I build the database (something which I thought would be easier to do at run-time rather than at the time I build the DB, but it seems as if it's not)


Thanks for all the help so far, guys I will use some of the suggestions to aid me in building the music table in the first place


Garry

 

[Mr.IncrediblyBored]

edit: nevermind, I type too slowly.

 

[Confused]

KnightBreed, no, you were right originally, I just didn't explain it very well originally, look at my post above yours.

edit, I see you've seen it


Garry

 

[Mr.IncrediblyBored]

Originally posted by: Confused
edit, I see you've seen it

If you change your mind and you decide to do this at runtime, it can definitely be done. The select will be a little more complicated, obviously, but it still won't be that bad.

 

[Confused]

Thanks for all your help, KnightBreed, but I think it will be easier to do it when I build/update the DB, as I'm displaying the Genre on the main player screen too, so I don't have to do any lookups in another table when the song changes, I can just pull another field out of the current record in the music table


Garry

 

[DBL]

Originally posted by: Confused
Thanks for all your help, KnightBreed, but I think it will be easier to do it when I build/update the DB, as I'm displaying the Genre on the main player screen too, so I don't have to do any lookups in another table when the song changes, I can just pull another field out of the current record in the music table


Garry

You say this as if a simple Select statement is going to make a noticeable difference in performance. Keeping redundant information in the music table violates the relational database design. Not that it matters either way, since this is such a simple situation. Personally, I would keep it as you originally had it.

 

[Confused]

Originally posted by: DBL

Originally posted by: Confused
Thanks for all your help, KnightBreed, but I think it will be easier to do it when I build/update the DB, as I'm displaying the Genre on the main player screen too, so I don't have to do any lookups in another table when the song changes, I can just pull another field out of the current record in the music table


Garry

You say this as if a simple Select statement is going to make a noticeable difference in performance. Keeping redundant information in the music table violates the relational database design. Not that it matters either way, since this is such a simple situation. Personally, I would keep it as you originally had it.

But, I can make this simple select statement once only, rather than every time I browse for songs to add to the playlist, play a new song etc. If the genreid field in the music table was only going to contain the id's and not the names as it does for some file formats (as i have described before) then it would probably be better to do it when I need to display them, but as it isn't, it makes it more difficult.


Garry

 

[DBL]

Originally posted by: Confused

But, I can make this simple select statement once only, rather than every time I browse for songs to add to the playlist, play a new song etc. If the genreid field in the music table was only going to contain the id's and not the names as it does for some file formats (as i have described before) then it would probably be better to do it when I need to display them, but as it isn't, it makes it more difficult.


Garry

I guess I'm a little confused. Why not have your simple select statement also join the genre with the text description in the query? You would still have to run the statement only once and you would be following more traditional design.