Finding the error on SQL query when learning Inner join multitable

user_13046123 0
Viewed 48

I am learning SQL and when doing some exercises this question showed up to me. In matter of Multi-table consults I see sometimes that I can use Where clause instead of inner join, but Í try to use join because is the topic im trying to learn now. In this example I wrote this:

select p.nombre from pokemon p join pokemon_forma_evolucion pfe 
on p.numero_pokedex = pfe.numero_pokedex
join forma_evolucion fo 
on pfe.id_forma_evolucion = fo.id_forma_evolucion
join tipo_evolucion t
on t.tipo_evolucion = fo.tipo_evolucion
where lower(t.tipo_evolucion) = 'intercambio';

but it shows nothing. ( 0 results )

in the Exercises the correct answer by the blogger is this one and works fine showing 4 results :

select p.nombre
from pokemon p, pokemon_forma_evolucion pfe, 
forma_evolucion fe, tipo_evolucion te
where p.numero_pokedex = pfe.numero_pokedex 
and pfe.id_forma_evolucion = fe.id_forma_evolucion
and fe.tipo_evolucion = te.id_tipo_evolucion
and lower(te.tipo_evolucion) = 'intercambio';

I would like to know why mine is not working and how should I use the JOIN command properly. Link of the Exercises blog with DER and access to entire "pokemon" SQL database: https://www.discoduroderoer.es/ejercicios-propuestos-y-resueltos-consultas-sql-bd-pokemon/

Thanks for the help. I am new on posting in this community but it is not the first time I used and enjoyed it. Thanks for all

inner-join mysql sql
1 Answers

the where and join should word fine. the problem is that you are joining the table "tipo_evolucion" ON the wrong field

fild Tipo_Evolucion in table FORMA-EVOLUCION is numeric (this is the foreing key). Fild Tipo_Evolucion in table TIPO_EVOLUCION is varchar2 (this is not the primary key)

the primary key in TIPO_EVOLUCION IS id_tipo_evolucion

So your inner join should be 

JOIN tipo_evolucion t ON t.id_tipo_evolucion  = fo.tipo_evolucion

the query therefore is:

SELECT p.nombre
FROM pokemon p
     JOIN pokemon_forma_evolucion pfe ON p.numero_pokedex = pfe.numero_pokedex
     JOIN forma_evolucion fo ON pfe.id_forma_evolucion = fo.id_forma_evolucion
     JOIN tipo_evolucion t ON t.id_tipo_evolucion  = fo.tipo_evolucion
WHERE LOWER(t.tipo_evolucion) = 'intercambio';
user_28847910
Related