I have reconstructed my database using the normal form I learned, but now I have problem joining them back into one with all the tables.

I have a song database, the connection of tables are linked like this:

songs          artists            song_vocals         song_composers
--------------------------------------------------------------------
song_id        artist_id          song_id             song_id
title          artist_name        artist_id           artist_id

There are multiple tables with their foreign key pointing to the artist table.

And I want to display all the information with name like this, I don't want display them by id:

song_id        title         vocal          composer
--------------------------------------------------------
1              ABC           John           Cat

So far the best I can do is left joining 3 tables, which only gives me song_id, title and vocal:

SELECT songs.song_id, songs.title, artists.artist_name as vocal FROM songs
LEFT JOIN song_vocals ON
song_vocals.song_id = songs.song_id
left join artists ON
song_vocals.artist_id = artists.artist_id

How can I join more?

You can create a table for composers if you haven't done so and add the foreign key in your song_vocals table which from your statement is your main table anyway. Then you can add another left join to the composer table after artists though don't forget to put the composers.composer_name (I assume) in your select statement as well.