MySQL join two tables and add column values

My knowledge of MySQL is not very in depth. If I have two tables that for example look like this:

Table1
Date        v1  v2  v3
05/01/2010  开发者_运维技巧26  abc 45
05/02/2010  31  def 25
05/03/2010  50  ghi 46

Table2
Date        v1 v2  v3
05/01/2010  42 jkl 15
05/02/2010  28 mno 14
05/03/2010  12 pqr 64

How can I join them in a query by their date and have the sum of table1.v1 and table2.v1 and also have the sum of table1.v3 and table2.v3? V2 should be ignored.

You may want to do the following:

SELECT    t1.date, t1.v1 + t2.v1 AS v1_sum,
          t1.v3 + t2.v3 AS v3_sum
FROM      table1 t1
JOIN      table2 t2 ON (t1.date = t2.date);

And if you want the total aggregate of both columns, you can also do as follows:

SELECT    SUM(t1.v1 + t2.v1) AS v1_sum,
          SUM(t1.v3 + t2.v3) AS v3_sum
FROM      table1 t1
JOIN      table2 t2 ON (t1.date = t2.date);

Test case:

CREATE TABLE table1 (`date` date, `v1` int, `v3` int);
CREATE TABLE table2 (`date` date, `v1` int, `v3` int);

INSERT INTO table1 VALUES ('2010-05-01', 26, 45);
INSERT INTO table1 VALUES ('2010-05-02', 31, 25);
INSERT INTO table1 VALUES ('2010-05-03', 50, 46);

INSERT INTO table2 VALUES ('2010-05-01', 42, 15);
INSERT INTO table2 VALUES ('2010-05-02', 28, 14);
INSERT INTO table2 VALUES ('2010-05-03', 12, 64);

First query result:

+------------+--------+--------+
| date       | v1_sum | v3_sum |
+------------+--------+--------+
| 2010-05-01 |     68 |     60 |
| 2010-05-02 |     59 |     39 |
| 2010-05-03 |     62 |    110 |
+------------+--------+--------+

Second query result:

+--------+--------+
| v1_sum | v3_sum |
+--------+--------+
|    189 |    209 |
+--------+--------+

create table corse ( id int(11), cname varchar(10),
cdet varchar(10) );

then create another table

create table stud ( id int(11),
sname varchar(10),
ssub varchar(10)
);

and then add primary key on table stud

alter table stud add primary key(id);

and then applied foreign key on corse

alter table corse add foreign key(id) references stud(id);

thats it last and final step

select s.id,s.sname,c.cname,c.cdet from stud s join corse c o n s.id=c.id;