0

This is the self-join query:

select g.discription, m.title 
from genre g 
join genre m on g.title = m.main_title

the result of self-join (table 1)

This is the table that I want to join:

select band.name, band.genretitel
from music_band as band 

Table 2

the result should be like this: result My code is:

select band.name, band.genretitel
from music_band as band
left join (
select g.discription, m.title 
from genre g 
join genre m on g.title = m.main_title) disc
on band.genretitel = disc.title

and I get the wrong outcome :  my query result

New contributor
Ibrahim Saleh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • Hi, and welcome to dba.se! Please do not post images for the reasons outlined in this link. Also, in future, please consider using a fiddle (dbfiddle.uk) for you tables and data! Nov 24 at 7:49
0

I do not think you need a self-join. LEFT JOIN allows the 'right' table to have no matching row, hence NULLs.

SELECT b.name AS Band,
       b.genretitel AS Genre,
       g.discreption AS 'Main Genre Description'
    FROM music_band AS b
    LEFT JOIN genre AS g  ON g.title = b.genretitel

Or...

SELECT b.name AS Band,
       b.genretitel AS Genre,
       ( SELECT g.discreption
            FROM genre AS g  WHERE g.title = b.genretitel
       ) AS 'Main Genre Description'
    FROM music_band AS b

In this case, the subquery will return NULL since there is no entry for guitar.

(In the future, please try to use text, not images.)

Your Answer

Ibrahim Saleh is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.