连接查询

left join 左连接 以左表为准，但是如果右表也有查询条件时，需要特别注意：

 

比如这样，只能获取到左右表都符合条件的数据：

SELECT a.id,b.status FROM bw_home_message a

right join bw_home_message_log as b

on a.id=b.home_message_id

WHERE a.home_id = 0 AND a.type = 2 AND b.home_id = '2'

 

如果只是以左表为准的话需要修改一下：

SELECT a.id,b.status FROM bw_home_message a

left join

（SELECT home_message_id,status FROM bw_home_message_log where home_id=2） as b

on a.id=b.home_message_id

WHERE a.home_id = 0 AND a.type = 2

 

Thinkphp中：

$where = array('a.home_id' => 0, 'a.type' => 2);
$logTable = C('DB_PREFIX') . 'home_message_log';

$sql = "SELECT home_message_id,status FROM {$logTable} where home_id=" . $home_id;

$info = M('home_message')->alias('a')->join("left join ($sql) as b on a.id=b.home_message_id")->where($where)->field('a.id,b.status')->select();