recursive query in related tables using foreign keys

Navigation
2

There is a database named Register and in this database there are three tables COUNTRY , CHILDREN and :

IntheCOUNTRYtableIhavetwosignatures:

TogetherwiththeCHILDRENtable:

andtheNETOStable

MytableNETOSisrelatedtothetableCHILDRENcolumnstrong>idFILHOSandmytableCHILDRENisrelatedtotableCOUNTRYbycolumnidPAIS.

SofarIthinkIdideverythingright,thisinitialdataisfortestingpurposesonly,butlet'ssupposethatthesetableswillhavethousandsofentriesandIwanttomakeaquerybythenameofthatperson,andinthatquerybringtherelationfamilyofthisperson,regardlessofthetableyouarelookingfor.

Example1:

Isearchfor%MATILDE%

MATILDEGONZAGAEFILHADEJOSEFIRMINOFRAGAENETADESEVERINOFIRMINOSILVA

Example2:

Isearchfor%MARIA%

MARIALEITAOGONZAGAEFILHADEJOSEFINAMONICALEITAOEMAEDEHEITORDOSANJOS#eutambémteriaqueterumacolunareferenteaosexo#masissonãovemaocasoagora

Inshort:Iwanttoreturnthe"name" field of all related tables resulting from my query.

If it was not clear, ask me in the comments.

    
asked by anonymous 14.06.2016 / 02:40

1 answer

1

The name of this is join, see example.

You should use the CONCAT function 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 from putting everything together in one column.

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

    
14.06.2016 / 02:47
Recursive function call - Python How to edit and create new record in DB? Ruby on rails