MySQL Join Condition

Question

I'm having trouble thinking of a way to do the following join in MySQL. I'm not sure which joins would be best suited for this task, so I'll edit the title when someone points it out. Here's the gist of what I'm trying to do.

I have two tables, call one Students, and the other Marks.
They are setup as follows,

Students

Only the Id field is unique

  .----+-----------------+--------+--------.
  | Id | Name            | Parent |  Mark  |
  +----+-----------------+--------+--------+
  |  1 | Name goes here1 |     0  |     0  |
  |  2 | Name goes here2 |     0  |    20  |
  |  3 | Name goes here3 |     2  |    45  |
  |  4 | Name goes here4 |     2  |    50  |
  |  5 | Name goes here3 |     1  |    20  |
  |  6 | Name goes here1 |     0  |    65  |
  .----+-----------------+--------+--------.

Marks

Id and Name are unique

  .----+-----------------+--------.
  | Id | Name            |Ranking |
  +----+-----------------+--------+
  |  1 | Name goes here1 |    20  |
  |  2 | Name goes here2 |    60  |
  |  3 | Name goes here3 |    90  |
  |  4 | Name goes here4 |   200  |
  |  5 | Name goes here5 |    45  |
  |  6 | Name goes here6 |    76  |
  .----+-----------------+--------.  

Now, what I need is as follows.
1. I need to join Students on itself so that Students.Parent=Students.Id
2. In the above join I only want to select the row where Students.Mark (S2) is the highest under that parent. Also, only join if Students.Mark >= 20 (Also S2).
3. I want to join the previous Student.Name on Marks.Name (From S1), selecting the Ranking.

Result

  .----+-----------------+--------+--------+--------+----------.
  | Id | Name            | Parent |  Child |  Mark  |  Ranking |
  +----+-----------------+--------+--------+--------+----------+
  |  1 | Name goes here1 |     0  |     5  |   20   |     20   |
  |  2 | Name goes here2 |     0  |     4  |   50   |     60   |
  .----+-----------------+--------+--------+--------+----------.

I think(?) this is possible using one query, but am not certain.

Answer 1

This query should do what you are looking for.

SELECT 
    s1.Id, s1.Name, s1.Parent, s2.Id as Child, MAX(s2.Mark) as Mark, m.Ranking 

FROM 
    Students s1
    INNER JOIN Students s2 ON (s1.id = s2.parent AND s2.Mark >= 20) 
    LEFT JOIN Marks m ON (s1.name = m.name) 

GROUP BY 
    s1.Id, s1.Name, s1.Parent, Child, Ranking;