Retrieve row where sum of column is greater than other column of different table

Question

I have two database tables.

First table:

| ID  |  Sub-Name |  Marks
| 01  |  french   |  50
| 01  |  russian  |  50
| 02  |  french   |  30
| 02  |  russian  |  50
| 03  |  french   |  20
| 03  |  russian  |  30

Second table:

|  ID | Stu-name | passing_marks
|  01 | abc      | 90
|  02 | xyz      | 90
|  03 | mno      | 90

I want to get the names of students whose collective marks of French and Russian are greater the passing_marks of table2.

Answer 1

Please , First declare primarykey and foreignkey relation with table with proper table name

Here I take 1st named detail and second table name is student

SELECT student.ID, student.Stu_name, detail.collective
FROM student
JOIN  
(
   SELECT ID, SUM(Marks) AS collective
   FROM detail GROUP BY ID
) detail
ON student.ID = detail.ID
AND detail.collective> student.passing_marks;

Answer 2

Try to join with sub-query like this:

SELECT t2.ID, t2.Stu_name, t1.Total 
FROM Table2 t2
JOIN  
(
   SELECT ID, SUM(Marks) AS Total
   FROM Table1 GROUP BY ID
) t1
ON t2.ID = t1.ID
AND t1.Total > t2.passing_marks;

Output:

| ID | STU_NAME | TOTAL |
-------------------------
|  1 |      abc |   100 |

See this SQLFiddle