mysql中如何查找同时存在于多个表中的数据？

Question

有class1,class2,class3三个表，代表3个不同的班级。表的结构都一样，有name和birthday两个字段，分别表示学生的姓名与出生日期。

class1数据：

name    birthday
james   1999-12-12
emma    1999-10-29
tom     2000-01-12
paul    1999-09-30

class2数据：

name    birthday
tom     1997-01-09
john    1996-12-10
mike    1997-03-12
larry   1996-08-10

class3数据：

name    birthday
peter    1998-03-12
tom      1998-08-31
milly    1997-05-12
benjamin 1997-12-12

那么如何才能找出同时存在于三个class中的名字。（在这里就是tom。）

Answer 1

select * from (class1 c1 join class2 c2 on c1.name=c2.name) join class3 c3 on c1.name=c3.name;

实际效果

(root)mytest-3306>>select c1.name c1name, c1.birthday c1birthday,
    -> c2.name c2name, c2.birthday c2birthday,
    -> c3.name c3name, c3.birthday c3birthday
    -> from (class1 c1 join class2 c2 on c1.name=c2.name)
    -> join class3 c3 on c1.name=c3.name;
+--------+---------------------+--------+---------------------+--------+---------------------+
| c1name | c1birthday          | c2name | c2birthday          | c3name | c3birthday          |
+--------+---------------------+--------+---------------------+--------+---------------------+
| tom    | 2000-01-12 00:00:00 | tom    | 1997-01-09 00:00:00 | tom    | 1998-08-31 00:00:00 |
+--------+---------------------+--------+---------------------+--------+---------------------+
1 row in set (0.00 sec)

Answer 2

这个就涉及到了MySQL的多表联合查询;

select a.name b.name as b_name from class2 as a,class3 as b where a.name=b.name

这是一个最简单的两个表的联合查询，多表联合查询的话，就是拿另一个表class1 作为a,calss2和class3的结果作为b再次进行联表查询如下，多表以此类推

select a.name from class1 as a,(select c.name,d.name as d_name from calss2 as c,class3 as d where c.name=d.name) as b where a.name=b.name

这是我给出的建议，不知道符不符合你的要求，有不对的地方欢迎指出。。。。