主表查子表中数据

现有如下两张表，主表和子表
主表：

HTML code

id    user_id    user_pwd    user_email
1    alexis    1111            123@126.com
2    ab    1234            1234@msn.com

子表：

HTML code

edu_id    user_id    edu_name    edu_date
1    1    大学            2010-10-01 00:00:00
2    1    高中            2006-09-01 00:00:00

通过主表的id和子表的user_id关联，
要求查询每个用户最高的学历信息，即查出来的结果是

+
--
----+---------+----------+--------------+----------+---------------------+


|
 id   
|
 
user_id
 
|
 user_pwd 
|
 user_email   
|
 edu_name 
|
 edu_date            
|


+
--
----+---------+----------+--------------+----------+---------------------+


|
    
1
 
|
 alexis  
|
 
1111
     
|
 
123
@126
.com  
|
 大学     
|
 
2010
-
10
-
01
 
00
:
00
:
00
 
|


|
    
2
 
|
 ab      
|
 
1234
     
|
 
1234
@msn
.com 
|
 
NULL
     
|
 
NULL
                
|


+
--
----+---------+----------+--------------+----------+---------------------+





有如下几种实现方法：



1. 

mysql
>
 
select
 
*

    
->
 
from
 (
    
->
  
select
 a.id,a.
user_id
,a.user_pwd,a.user_email,b.edu_name,b.edu_date
    
->
  
from
 users a 
left
 
join
 edu b 
on
 a.id
=
b.
user_id

    
->
  
order
 
by
 a.id,b.edu_date 
desc

    
->
 ) t
    
->
 
group
 
by
 id;

2.


SELECT * FROM `users` a1 LEFT JOIN (

SELECT a.user_id,a.edu_name,a.edu_date FROM edu a

LEFT JOIN edu b ON a.user_id=b.user_id AND a.edu_date>=b.edu_date  

GROUP BY a.user_id,a.edu_name,a.edu_date HAVING COUNT(*)=2) b1 ON a1.id=b1.user_id

3.

SELECT
 users.
*
,
(
SELECT
 edu_name 
FROM
 edu 
WHERE
 edu.
user_id
=
users.id  
ORDER
 
BY
 edu_date 
DESC
 LIMIT 
1
) 
AS
 name1

FROM
 users