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

1.

select * from Students S1
join Students S2 on(S1.Id=S2.StudentId)

2.

You need to clarify which of the two instances of Students you mean here in each of the two cases -- S1 or S2? As you express your (homework?) assignment, it's 100% ambiguous in each case. Could be where S1.Mark>=20 and S1.Mark=(select max(s3.mark) from students s3 etc etc, or many other variations.

1. again the ambiguity about what instance of Students you mean, anyway you just need to alter the select and add one join:

   select Marks.Ranking from Students S1 join Students S2 on(S1.Id=S2.StudentId) join Marks on(S1.Name=Marks.Name)

and the where clause as before.

Answer 2

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;