Finding grand parents of children

It's been a few days I'm trying to understand how inner join works.

I have two simple tables (family bonds):

"persons" (id, fname, lname)
"relationships" (parent/child)

I learned how to get all parents of children

SELECT p.firstname, p.lastname, c.firstname, c.lastname  
FROM persons p
INNER JOIN relationships ON p.id = relationships.parent 
INNER JOIN persons c     ON c.id = relationships.child;

What about getting all the grand parents of children instead?

Is it just an adjustment to that query or is it more involved? It seems to me that I should apply the same query to the query (a one time recursiveness) but I don't see how.

Thanks for your guidance.

PS: inner joins are easy to understand on paper but very hard for me to use because I'm used to procedural algorithms in order to create scripts but inner joins are not procedural at all in the way they should be approached. So I'm trying to analyse use cases to get used to them

Solution

What about getting all the grand parents of children instead?

First, your query, but with a better name for relationships:

SELECT p.firstname, p.lastname, c.firstname, c.lastname  
FROM 
  relationships p_c 
  INNER JOIN persons p ON p.id = p_c.parent 
  INNER JOIN persons c ON c.id = p_c.child;

Then to get a grandparent:child you have to route through parent:

SELECT p.firstname, p.lastname, c.firstname, c.lastname  
FROM 
  relationships p_c 
  INNER JOIN relationships gp_p ON gp_p.child = p_c.parent --in a grandparent:parent relationship the child of the grandparent, is the parent of the grandchild in the parent:child
  INNER JOIN persons gp ON gp.id = gp_p.parent --parent = the grandparent
  INNER JOIN persons c ON c.id = p_c.child;    --child = the grandchild

Not sure i'd have picked on this example to learn inner joins.. Also it'd probably be easier to think about if relationships columns were called something else, like elder and younger :)