Multiple inner JOIN on the same table

Question

I have this table 'individu'

and this table

I want to do a select and get in the query, the 'nom' and 'prenom' for iid1 and iid2.

For the moment my query is

SELECT * FROM lien 
inner join individu on individu.id = iid1 
WHERE individu.famille_id = 1002 
AND iid1 = 1

But with that I only got the iid1's name

Answer 1

I think this is maybe what you want based on what I understood from the tables:

SELECT a.famille_id, 
      a.iid1,
      b.prenom prenom1,
      b.nom nom1,
      a.iid2,
      c.prenom prenom2,
      c.nom nom2
FROM lien a
left join individu b on b.id = iid1 and b.famille_id  = a.famille_id 
left join individu c on c.id = iid2 and c.famille_id  = a.famille_id 
where b.famille_id = c.famille_id