Query in three tables by a column with Foreign Key

Question

This was the way I found to make a query in my three tables: PARENTS, CHILDREN and GRANDCHILDREN using the operator LIKE:

SELECT `PAIS`.nome,
       `FILHOS`.nome,
       `NETOS`.nome 
 FROM  `PAIS`, `FILHOS`, `NETOS`
 WHERE `PAIS`.nome LIKE '%MATILDE%'
 OR    `FILHOS`.nome LIKE '%MATILDE%' 
 OR    `NETOS`.nome LIKE '%MATILDE%'

I have another question related to the problem: recursive query in related tables using Foreign Keys

Just to recap, I’m going over the definitions of how my database is doing again (see image below):

Note that the table FLHOS is related to the table PARENTS by the column idPAI, in the case JOSE is the son of SEVERINO and MARIA is the daughter of JOSEFINA:

Now the question that won’t shut up: how do I bring this relationship of id with the person’s name through the word search '%MATILDE%' using the operator LIKE?

Example:

MATILDE idFILHOS 1 means that she is the daughter of JOSE, who is the son of SEVERINO, did you understand? I want to make this "recursion" seeking nome, but bringing only those who are related by the primary key id.

Answer 1

Hello, you need to list the primary and foreign keys of the tables... Make an INNER JOIN.

NOTE: I put JOIN wrong. Follow correct code:

SELECT 
  PAIS.nome,
  FILHOS.nome, 
  NETOS.nome 
FROM PAIS 
INNER JOIN FILHOS 
  ON PAIS.id = FILHOS.idPAI 
INNER JOIN NETOS ON FILHOS.idPAI = NETOS.idFILHOS
WHERE 
  PAIS.nome LIKE '%MATILDE%' OR FILHOS.nome LIKE '%MATILDE%' OR NETOS.nome LIKE '%MATILDE%'