I have a countries table and another table with continents that has FK as the id of the countries table and I needed to return all the countries in Europe. Can anyone give a help on how to do this? Thank you in advance.
This is the structure of the table:
COUNTRIES
id_pais
Parents
CONTINENTS
id_continente continent id_pais (FK)
After the question was edited the following lines were rendered meaningless. Who should have an FK should be the table of countries and not the table of continents.
One country is that it belongs to a continent and not the other way around.
You should create the two tables as follows:
CREATE TABLE continentes (
id INT NOT NULL,
....
....
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE paises (
id INT,
continente INT,
.....
.....
PRIMARY KEY (id),
FOREIGN KEY (continentes)
REFERENCES continentes(id)
ON DELETE CASCADE
) ENGINE=INNODB;
SQL to list the mainland countries id = 5 would look something like this:
SELECT * FROM paises p
INNER JOIN continentes c ON c.id = p.continente
WHERE c.id = 5
Without its complete structure it is difficult to help you. But with the information you gave in the question, you can already find an error, you say
I have a countries table and another table with continents that have FK as the country table id
This is wrong, it is the other way around. Think the following, one country has several continents or one continent has several countries? Obviously a continent that has several countries, so the countries that are inserted in the continent and not the other way around.
Your tables need to look like this:
CONTINENTES PAISES
---------------------------------------
ID (PK) ID (PK)
DESCRICAO DESCRICAO
ID_CONTINENTE (FK)
And to bring all the countries of a continent, it is necessary to make a select using inner join . See more about inner join here .
SELECT P.DESCRICAO FROM PAISES P
INNER JOIN CONTINENTES C ON C.ID = P.ID_CONTINENTE
WHERE C.ID = 1