How to use subquery to query two tables-CodePudding

I am trying to provide an overview of all music bands with the genre and the description of the main genre.

The end result should be like in 2nd image

Here is my query,

select m.name, m.genretitle, g.description
from music band as m, genre as g
where m.genretitle = g.title and g.genre title_main genre 
in 
(
  select title
  from genre
);

But then I get the wrong output. What I am doing wrong here plz ?

thank you

CodePudding user response：

You should join two tables on title/genretitel (typo?)

# MySql syntax
SELECT m.name, m.genre_title, g.description
FROM music_bands m
JOIN genre g ON g.title = m.genretitel

CodePudding user response：

Another left join to genre should do it.

SELECT
  band.name AS `Band`
, band.genre_title AS `Genre`
, main.description AS `Main genre description`
FROM music_bands AS band
JOIN genre 
  ON genre.title = band.genretitle
LEFT JOIN genre AS main
  ON main.genre = genre.`genre title_main genre` 
 AND main.`genre title_main genre` IS NULL