自定义方式在codeigniter中编写连接，内连接mysql查询

Question

I have a query to select data from multiple tables. How do I write its equivalent code in codeigniter. See the query:

select * 
from A inner join B on (A.ad_no=B.ad_no) 
where  B.ad_no in (select ad_no 
                   from A 
                   where $staff!='00:00' and $staff!='0:00')  
order by B.ctype asc, B.cname asc,B.ad_no asc

I tried a query in codeigniter but its taking longer time to load the result.

Answer 1

You can try the following (i removed the $ sign from staff)

$query = $this->db
    ->select("*")
    ->from("A")
    ->join("B", "A.ad = B.ad_no")
    ->where("B.ad_no in (select ad_no from A where staff!='00:00' and staff!='0:00')",NULL, false)
    ->order_by("B.ctype", "ASC")
    ->order_by("B.cname", "ASC")
    ->order_by("B.ad_no", "ASC")
    ->get();

You get a generated output with the following statement

echo $this->db
    ->select("*")
    ->from("A")
    ->join("B", "A.ad = B.ad_no")
    ->where("B.ad_no in (select ad_no from A where staff!='00:00' and staff!='0:00')",NULL, false)
    ->order_by("B.ctype", "ASC")
    ->order_by("B.cname", "ASC")
    ->order_by("B.ad_no", "ASC")
    ->get_compiled_select();