complex query in mysql

Question

i have three tables in mysql like this,

triz_sti

stu_id    name
-----------------
1          x1
2          x2


triz_sub

sub_id    sub_name
------------------
1          english
2          maths
3          science


triz

stu_id   sub_id   marks
-------------------------
1          1       23
1          2       56
1          3       83
2          1       78
2          2       23
2          3       50

i want the result like display all subject with higest mark in perticular subject with student name,

max_marks    sub_name     student_name
--------------------------------------
78            english     x2
56            maths       x1
83            science     x2

so please help for this output that i want, i have tried but i m not get it desire output.

Answer 1

The general, simplified syntax in this case is

SELECT stuff FROM joined tables ORDER BY whatever

The easiest is the ORDER BY: you want to sort descending by marks, so you ORDER BY marks DESC.

Where do the data come from? From triz, joined to the others. So

triz JOIN triz_sti USING (stu_id) JOIN triz_sub USING (sub_id)

And you want to display the marks.

So you get

SELECT marks, sub_name, name AS student_name
    FROM triz JOIN triz_sti USING (stu_id) JOIN triz_sub USING (sub_id)
    ORDER BY marks DESC

.

The rest I leave to you. :-)

Answer 2

How about something like this?

SELECT 
  t.stu_id, t.sub_id, t.marks
FROM
triz t
JOIN (SELECT sub_id, MAX(marks) max_mark FROM triz GROUP BY sub_id) a ON (a.sub_id = t.sub_id AND a.max_mark = t.marks)

Of course you'll need to join it with lookup tables for names.

Have to say, it's early here so I might have missed something.

BR