How to join an exsiting table to self-join query table?

31
November 25, 2021, at 01:30 AM

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

Answer 1

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
Answer 2

An extra left join to genre will get the 2nd level genre descriptions.

Home Brew Sample Data:

create table genre (
 title varchar(30) not null primary key, 
 description varchar(100), 
 main_title varchar(30), 
 foreign key (main_title) references genre(title) 
)
-- First level genres
insert into genre
(title, description) values
  ('Guitar', 'It uses guitars')
, ('Hip Hop', 'Lots of words') 
;
-- Second level genres
insert into genre
(title, description, main_title) values
  ('Rock', 'Rocky guitar sounds', 'Guitar')
, ('Blues', 'Feeling blue', 'Guitar')
, ('Jazz', 'Jamming and more', 'Guitar')
-- Third level genres
insert into genre
(title, description, main_title) values
  ('Art Rock', 'Artsy rocky sounds', 'Rock')
, ('Heavy Metal', 'Loud metal works', 'Rock')
, ('Pop/Rock', 'Rocky pop tunes', 'Rock')
, ('Hawai Hip Hop', 'Under the coconuts', 'Hip Hop')
, ('Latin Rap', 'Mucho gusto', 'Hip Hop')
-- Only one level deep
select 
  g.title as genre_title
, g.description as genre_description
, m.title as main_title
, m.description as genre_description
, m.main_title as main_main_title
from genre g 
join genre m on m.title = g.main_title
order by m.title, g.title
genre_title genre_description main_title genre_description main_main_title
Blues Feeling blue Guitar It uses guitars null
Jazz Jamming and more Guitar It uses guitars null
Rock Rocky guitar sounds Guitar It uses guitars null
Hawai Hip Hop Under the coconuts Hip Hop Lots of words null
Latin Rap Mucho gusto Hip Hop Lots of words null
Art Rock Artsy rocky sounds Rock Rocky guitar sounds Guitar
Heavy Metal Loud metal works Rock Rocky guitar sounds Guitar
Pop/Rock Rocky pop tunes Rock Rocky guitar sounds Guitar
create table music_bands (
 id int auto_increment not null primary key,
 name varchar(100) not null,
 genre_title varchar(30), 
 foreign key (genre_title) references genre(title) 
);
insert into music_bands (name, genre_title) values
  ('Metallicos', 'Heavy Metal')
, ('Paul McSnare', 'Guitar')
, ('The Cobbles', 'Rock')
, ('Charlie Davis', 'Jazz')
, ('John Lee Walker', 'Blues')
, ('Lil Chino', 'Latin Rap')
, ('Snoop Crab', 'Hawai Hip Hop')
, ('The Why', 'Art Rock')
, ('U42B1', 'Pop/Rock')

The Query:

SELECT
  band.name AS `Band`
, band.genre_title AS `Genre`
, genre2.description AS `Main genre description`
FROM music_bands AS band
LEFT JOIN genre AS genre1
  ON genre1.title = band.genre_title
LEFT JOIN genre AS genre2
  ON genre2.title = genre1.main_title
ORDER BY genre2.title, band.genre_title, band.name
Band Genre Main genre description
Paul McSnare Guitar null
John Lee Walker Blues It uses guitars
Charlie Davis Jazz It uses guitars
The Cobbles Rock It uses guitars
Snoop Crab Hawai Hip Hop Lots of words
Lil Chino Latin Rap Lots of words
The Why Art Rock Rocky guitar sounds
Metallicos Heavy Metal Rocky guitar sounds
U42B1 Pop/Rock Rocky guitar sounds

db<>fiddle here

READ ALSO
Python is not able to read the file

Python is not able to read the file

Let me rephrase the questionThe equal signs are in the code, i hope that this is showed in the following lines of code

41
Goods list Data import using Excel Sheet

Goods list Data import using Excel Sheet

I'm new to Greater WMSI'm experimenting problems related to Importing Goods List using Excel file

53
PHP social contribution Yearly [closed]

PHP social contribution Yearly [closed]

Want to improve this question? Update the question so it focuses on one problem only by editing this post

39