不能选择左表和连接表输出json_encode - Can not SELECT LEFT and JOIN tables with output json_encode

Can not SELECT LEFT and JOIN tables with output json_encode

我试图通过使用'select left'在表中获取id的两个字符，我试图与id参数连接到另一个表，但在执行时不运行，并以json的形式输出结果，但它不起作用。

我的脚本,

Controllers
        foreach($this->mymodel->getLeft()->result_array() as $row)
            {   
                $data[] = array(
                    'sub_id' => $row['sub_id'],
                    'long_id' => $row['long_id'],
                    'total' => $row['total']
                  );                
            }   
            echo json_encode($data);

模型:

function data_alat() {
    $sql = "SELECT LEFT(tb1.id,2) AS sub_id, long_id, COUNT(*) total 
            FROM tb1 JOIN tb2 ON tb2.id = tb1.sub_id 
            GROUP BY sub_id ";
    return $this->db->query($sql);
}

我数据库:

tb1:

field id=
BM-001,
BM-002,
BC-009,
JK-001,
LP-080,
JK-002

tb2:

field id=
BM,
BC,
JK,
LP
field long_id=
BIM,
BIC,
JIK,
LIP

我期望{"sub_id":"BM"、"long_id":女子,"总":"2"}

try this

$sql = "SELECT LEFT(tb1.id,2) AS sub_id, COUNT(*) total 
            FROM tb1 LEFT JOIN tb2 on left (tb1.id,2) = tb2.sub_id 
            GROUP BY sub_id ";