mysql - 相关查询的内连接结果

Question

我有这样的问题

SELECT hc_num,client_pow,client_id,date_issued,client_occup 
FROM tbl_health_certificate t1 
WHERE date_issued = 
    (SELECT MAX(date_issued) 
     FROM tbl_health_certificate t2 
     WHERE t2.client_id=t1.client_id) AND year(date_issued) < year(now())

现在我希望将结果连接到另一个表，以便我获取一些行值，特别是 client_fname 和 client_lname 字段。我将如何做？ :)

我的表格结构如下 --> tbl_health_certificate , [tbl_client][2]

Content of tbl_health_certificate

应该显示的结果是带有 client_id 的行:-26-29

使用表 tbl_client 中的 client_fname 和 client_lname

Answer 1

修改了几行。

select table1.* , table2.* from
(SELECT hc_num,client_pow,client_id,date_issued,client_occup 
FROM tbl_health_certificate t1,
(SELECT MAX(date_issued) date_issued_max
     FROM tbl_health_certificate t2 group by client_id) t2
WHERE t1.date_issued = t2.date_issued_max
AND year(date_issued) < year(now())) table1
left/right/full outer join -- use appropriate join
tbl_client table2 ON table1.client_id = table2.client_id