Alfabetic list from more than one database fields ?

MichaelHe

Is it possible list an alfabetic list with names from two different database fields.
An ex. could be a database like this with a song title in field 1, a composer in field 2, and another composer in field 3.

She Loves You, Paul McCartney, John Lennon
Yesterday, Paul McCartney, blank
I Got My Mind Set On You, George Harrison, blank

Her the 3rd field is only in use in the first post.
And then I have to make a list looking like this

George Harrison
John Lennon
Paul McCartney

As you can see, Paul McCartney is only listed ones, though he figures in to posts, and John Lennon is shown alfabetic, even though he is only shown in field 3, and the other two is shown in field 2.

This could be a more advance database with maybe 1000 songs, and 4 fields with composer names, and all the composers written in field 2,3,4,5 should then be listed only ones in alfabetic order no matter in what field their name is written.

Is this possible and how do I do that. ??

Michael

inerte

You need to change this method to store the song composers. Not because there isn't a way to make it work with 1,2,3,4,5 columns, but because Relational Databases aren't supposed to work this way 🙂

Use another table, linking the composer name and the song id. You gotta have an id for the songs because of duplicate names.

The new table will represent the relatioship between songs and composers, allowing you to have any number of composers linked to songs (even zero). It has only two columns, "song_id" and "composer_name".

Then you join the rows from both tables and can order by the composer name:

SELECT songs.name, songs_composers.composer_name FROM songs, song_composer WHERE songs.id = songs_composer.song_id ORDER BY songs_composer.composer_name;

If this is too complicated (it should not be, and you need to know how to model relational data with a table to express these relationships, it's very important), then just try a GROUP BY on the original table. ORDER BY with GROUP BY is a little tricky, so be careful.