-1

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

I have two simple tables (family bonds):

  1. "persons" (id, fname, lname)
  2. "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

Bachir Messaouri
  • 754
  • 2
  • 8
  • 31
  • 1
    [CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) [Re SQL querying.](https://stackoverflow.com/a/33952141/3404097) [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) [Null in Relational Algebra](https://stackoverflow.com/questions/55663848/null-in-relational-algebra) [What to do with null values when modeling and normalizing?](https://stackoverflow.com/a/40733625/3404097) – philipxy Jul 04 '21 at 01:03
  • 1
    [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/q/27682228/3404097) [What is a self join for? (in english)](https://stackoverflow.com/a/37384306/3404097) – philipxy Jul 04 '21 at 01:10
  • 1
    PS Find out what a subquery is & so how to nest queries when serial joins are inadequate. – philipxy Jul 04 '21 at 01:39

1 Answers1

1

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 :)

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Thank you very much Caius. And thank you for the comments which help a lot! In my question, I needed each grand child to have his 4 GP listed in the same line than his (1 line = GC+GP1+GP2+GP3+GP4). Does it change the query completely since the select must probably be "SELECT c.fname, c.lname, gp1.fname, gp1.lname, gp2.fname, gp2.lname, gp3.fname, gp3.lname, gp4.fname, gp4.lname" ? Once again, thanks very much, this helps a lot. (and yes, it's advanced but I need these use cases to get the big picture). I will rename my columns so that it is clearer, you are right. – Bachir Messaouri Jul 04 '21 at 08:46
  • 1
    Do you mean 4 levels of ancestry? Or do you mean the 4 grandparents? Your query, for any given child, should produce 4 rows, one row per grandparent (2 male rows, 2 female rows, let's say). If you want those as columns, that's a pivot operation – Caius Jard Jul 04 '21 at 08:49
  • The 4 grand parents (if there are 4 grand parents of course) in the same row along with their grand child. Like this: "Grand child I GP1 I GP2 I GP3 I GP4" . It doesn't have to be grouped by sex, only the first 4 GP in the order they come according to their ID. Inner join is not enough then ? Would a subquery help ? Thank you Caius. – Bachir Messaouri Jul 04 '21 at 08:59
  • Can I use the result of the query (so far it is one line = GC+ one GP) and inner join it to itself so that I can retrieve all GP of the same GC in one line ? I'm thinking out loud in order to find solutions. – Bachir Messaouri Jul 04 '21 at 09:05
  • 1
    It's actually fairly tricky to get them all on one line, and probably not a great learning exercise in the context of inner joins. It would be best asked as a separate question because it needs a big block of code not suitable for a comment. I'd suggest to mark this question as answered (click the grey check next to my answer) and then pose another (using the code from this one, the results it gives and the desired output). Also state what database software you're using. I suspect we'll end up doing a conditional aggregation on row_number – Caius Jard Jul 04 '21 at 09:28
  • Ok marked as solved. I use sqlite3. This question is part of 4 exercises given at the end of a textbook chapter that only requires to know the clauses I mentioned in the beginning of my question. So I suppose there are ways to get there. but I'm surprised by the difficulty as it is supposed to be a low level course. I can only see 4 scenarios: using a subquery, inner joining the last result to itself, using EXISTS, using GROUP BY + Having. If it's not enough then there is some issue with the textbook (PS: the answers are not there, which is why I'm asking). Thank you very much for your help. – Bachir Messaouri Jul 04 '21 at 09:46
  • 1
    If you know group you can do it that way. Link to the new question you ask and i'll demo – Caius Jard Jul 04 '21 at 09:47
  • Super nice if a "group by" would do the trick. Here is the new question: https://stackoverflow.com/questions/68243523/sql-retrieve-all-grand-parents-at-once I declared at the bottom of the new question the clauses that I "can" use. – Bachir Messaouri Jul 04 '21 at 09:57