Query in three tables by a column with Foreign Key

Navigation
2

This was the way I found to query my three tables: COUNTRY , CHILDREN and NETBLES using the > 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 tables related using foreign keys

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

NotethattheFLHOStableisrelatedtothePARENTtablebycolumnidPAI,incaseJOSEisthechildofSEVERINOandMARIAisthechildofJOSEFINA:

  

Nowthequestionyoudonotwanttoshutup:HowdoIbringthisidrelationtotheperson'snamebysearchingfortheword'%MATILDE%'usingtheLIKE/p>

Example:

MATILDEidFILHOS1meansthatsheisthedaughterofJOSE,whoisthechildofSEVERINO,canyouunderstand?Iwanttodothis"recursion" by searching for nome , but only those that are related by the primary key id .

    
asked by anonymous 17.06.2016 / 19:57

1 answer

1

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

NOTE: I put the JOIN wrong. Here is the 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%'
    
17.06.2016 / 22:05
Class with more than one How to get xml data with php?