Inner Join and sub-query problems

Question

This my query below serves to mount a table with data from multiple tables, however is giving an error:

Error Code: 1242. Subquery Returns more than 1 Row 0,000 sec

I think and the problem is in this sub-query:

(select tblCategoria.nomeCategoria as nomeCategoria 
   from tblAnimeHentai 
  inner join tblCategoria on tblAnimeHentai.categoria = tblCategoria.idCategoria) as categoria

I think it loads all categories instead of loading the corresponding.

select tblAnimeHentai.nomeAnime           as nomeAnime
      ,tblAnimeHentai.descricaoAnime      as descricaoAnime
      ,tblAnimeHentai.episodiosAnime      as episodiosAnime
      ,tblAnimeHentai.tumbLocation        as tumbLocation
      ,(select tblCategoria.nomeCategoria as nomeCategoria 
          from tblAnimeHentai 
         inner join tblCategoria on tblAnimeHentai.categoria = tblCategoria.idCategoria) as categoria
      ,tblAnimeHentai.completo            as completo
      ,tblAnimeHentai.dataPublicacao      as dataPublicacao
      ,tblAnimeHentai.dataAdicionado      as dataAdicionado
      ,tblAnimeHentai.comentarioAnime     as comentarioAnime
      ,tblAnimeHentai.produtora           as produtora 
  from tblAnimeHentai;

What am I doing wrong? How can I correct the error?

Answer 1

I think what you need is a correlated subquery, try:

(select tblCategoria.nomeCategoria as nomeCategoria from tblCategoria where tblAnimeHentai.categoria = tblCategoria.idCategoria) as categoria,

As for your other doubt:

SELECT
    tblAnimeHentai.nomeAnime as nomeAnime, 
    tblAnimeHentai.descricaoAnime as descricaoAnime, 
    tblAnimeHentai.episodiosAnime as episodiosAnime,
    tblAnimeHentai.tumbLocation as tumbLocation, 
    (SELECT tblCategoria.nomeCategoria as nomeCategoria FROM tblCategoria WHERE tblAnimeHentai.categoria = tblCategoria.idCategoria) as categoria,
    tblAnimeHentai.completo as completo, 
    tblAnimeHentai.dataPublicacao as dataPublicacao, 
    tblAnimeHentai.dataAdicionado as dataAdicionado, 
    tblAnimeHentai.comentarioAnime as comentarioAnime,
    tblAnimeHentai.produtora as produtora 
FROM tblAnimeHentai
WHERE tblAnimeHentai.categoria = "categoria x";