Database Query Help required in MySQL

Question

I am looking for help in writing a query of retrieving the values from 2 tables in MySQL.

The scenario is

Table A

ID    Name    Marks
===================
23    John    67
45    Mark    45
12    Ram     87

Table B has the following Structure

ID    Name    Evaluation Marks
==============================
45    Mark    34
78    Chris   09
98    Nancy   10
23    John    12

I am trying to write a query, where if I execute the following query

Select "SOMETHING" from Table A where Id=45

I should get Marks Column as 45+34=79, which should fetch and sum from the both the Tables A and Table B.

If I execute the query with the Id=12. Since the Id=12, does not exists in the Table B, I should get the Marks as 87.

What would a query for the above?

Answer 1

I assume that the id occurs only once in your tables table a, but could be missing in both. If it always exists in table a, you can use a LEFT JOIN instead of the UNION.

SELECT COALESCE(SUM(marks), 0)
FROM
(
  SELECT marks FROM a WHERE id = 45
  UNION ALL
  SELECT SUM(evaluation_marks) AS marks FROM b WHERE id = 45
) x

Edit

If you have all users in table a, then use

SELECT a.marks + COALESCE( SUM( b.evaluation_marks ), 0 )
FROM a
LEFT OUTER JOIN b ON ( b.id = a.id )
WHERE a.id = 45
GROUP BY a.id, a.marks

You should consider changing your table model though. Why do you store name and id twice? Can't you do it like that:

id    name    marks    evaluation marks
=======================================
12    Ram     87        0
23    John    67       12
45    Mark    45       34
78    Chris    0        9
98    Nancy    0       10