How to get all data from one table based on another table?

Asked

Viewed 1,223 times

2

I have a countries table and another table with continents that has as FK the id of the countries table and I needed to return all countries of Europe. Can anyone give a help on how to do this? Thanks in advance.

This is the table structure:

COUNTRIES

id_pais
Parents

CONTINENTS

id_continente continent id_pais(FK)

mysql sql

  • You can edit your question with the table structure?

  • Without the table structure is kind of complicated, enjoy and create a sqlfiddle.

  • Did you manage? I was making a sqlfiddle, but if you have already I will not finish

  • I thought I did, but no. I updated with my tables

  • See my Sqlfiddle, you will have to change your tables.

2 answers

3

After editing the question the following lines became meaningless.
Who should have a FK should be the table of countries and not the table of continents.
A country belongs to a continent and not the other way around.

You must 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 continent 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

1

Without its complete structure it is difficult to help you. But with the information you gave in the question, you can already find a mistake, you say

I have a countries table and another table with continents having as FK the id of the countries table

This is wrong, it is the other way around. Think about this, does a country have several continents or does a continent have 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 stay that way:

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 the inner join here.

SELECT P.DESCRICAO FROM PAISES P
INNER JOIN CONTINENTES C ON C.ID = P.ID_CONTINENTE
WHERE C.ID = 1

See working on Sqlfiddle.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.