recursive query in related tables using Foreign Keys

Question

There’s a database with a name Register and on this bench there are three tables PARENTS, CHILDREN and GRANDCHILDREN:

On the table PARENTS I have two criminal records:

Along with the table CHILDREN:

and the table GRANDCHILDREN

My table GRANDCHILDREN this relates to table CHILDREN for spine idFILHOS and my table CHILDREN is related to table PARENTS for spine idPAIS.

So far I think I’ve done everything right, these initial data are for testing purposes only, but let’s assume that these tables will have thousands of entries and I want to make a query by the name of this person, and in this query bring the family relationship of this person, regardless of the table you are looking for.

Example 1:

I seek to %MATILDE%

MATILDE GONZAGA
E FILHA DE JOSE FIRMINO FRAGA
E NETA DE SEVERINO FIRMINO SILVA

Example 2:

I seek to %MARIA%

MARIA LEITAO GONZAGA
E FILHA DE JOSEFINA MONICA LEITAO
E MAE DE HEITOR DOS ANJOS

# eu também teria que ter uma coluna referente ao sexo
# mas isso não vem ao caso agora

Summarizing: I want to return the "name" field of all related tables resulting from my query.

If it’s not clear, ask me in the comments.

Answer 1

The name of this is Join, see the example.

You must use the function CONCAT to concatenate the fixed text.

SELECT N.NOME AS NOME,
CONCAT(' É FILHA DE ', F.NOME) AS NOME_FILHO,
CONCAT(' E NETA DE ', P.NOME) AS NOME_PAI
FROM NETOS N
INNER JOIN FILHOS F ON F.ID = N.IDFILHOS
INNER JOIN PAIS P ON P.ID = F.IDPAI
WHERE N.NOME LIKE '%MATILDE%' /*Só um exemplo de como usar o where*/

Obviously this will show the results in three columns, but nothing prevents you to put it all together in one column.

See more about the types of joins in this question: What is the difference between INNER JOIN and OUTER JOIN?