Rafael Botas Rafael Botas - 5 months ago 8
MySQL Question

SQL Query not retrieving anything

I have 2 tables:

Equipas:

codEquipa (which is the table's primay key)

nomeEquipa

logoEquipa

estadioEquipa

cidadeEquipa

Jogo:

codJogo (which is the table's primay key)

codEquipaCasa (which is the foreign key for codEquipa)

codEquipaFora (which is the foreign key for codEquipa)

dataJogo




And have a query:*

SELECT Equipa.nomeEquipa AS "Casa",
Equipa.nomeEquipa AS "Visitante"
FROM Equipa
JOIN Jogo AS j1 ON j1.codEquipaCasa=Equipa.codEquipa
JOIN Jogo AS j2 ON j2.codEquipaFora=Equipa.codEquipa


The thing is that i have one registry in Jogo with existant foreign keys but the query don't retrieve me anything.

Thanks in advance for any help you can give me.
Let me know if you need more info.

Answer

You want to join Equipa twice to Jogo, not the other way around:

SELECT ec.nomeEquipa AS "Casa", 
       ef.nomeEquipa AS "Visitante" 
FROM Jogo j JOIN
     Equipa ec
     ON j.codEquipaCasa = ec.codEquipa JOIN
     Equipa ef
     ON j.codEquipaFora = ef.codEquipa;

If there are missing values, then you might want LEFT JOIN.

Comments