JOIN with 3 tables

Question

I would like to get a list of singers that have at least 1 song on their albums:

CREATE TABLES singers
id_singer
name_singer

We list songs with their singers:

CREATE TABLE songs
id_song
id_singers // this is the id_singer in singers table

This table is used in case we have a same song for 2 or more singers

CREATE TABLE songs_join
id_sj
id_song_join // this is the id_song on songs table
id_singer_join // this is the id_singer in singers tables

So we need to look for a singer that his id exists in the songs list (singer.id_singer = songs_id_singers) and the songs.id_song should be equal to songs_join.id_song_join in one query.

Thanks in advance

Answer 1

Try this query.

SELECT name_singer FROM singers INNER JOIN songs ON songs.id_singers = singers.id_singer INNER JOIN songs_join ON songs_join.id_singer_join = singers.id_singer;

Answer 2

use

SELECT * FROM singers AS s
JOIN songs AS so ON so.id_signer = s.id_singer
JOIN songs_join AS sj ON sj.id_signer_join = s.id_singer
     AND sj.id_song_join = so.id_song