[SOLVED] Convert MySQL query containing UNIONed subqueries as the FROM clause in CodeIgniter DB syntax

Convert MySQL query containing UNIONed subqueries as the FROM clause in CodeIgniter DB syntax

I am trying to get data from database using Codeigniter as framework.

I was able to come up with SQL query for desired result, but since I am not familiar with CodeIgniter DB syntax, I am having a trouble. Below is the query that I want to execute. How do I do this in PHP?

SELECT A.*, B.*
FROM
    (SELECT A.*
    FROM DriversInfo as A, InvoiceQueue as B
    WHERE A.CreateTime = B.DriverCreateTime
    AND B.Error <> 1
    GROUP BY A.CreateTime
    UNION
    SELECT DISTINCT A.*
    FROM DriversInfo as A, OrderInfo as B
    WHERE A.CreateTime = B.DriverKey
    AND B.Invoice <> '0') as A
LEFT JOIN DriversDoc as B
on A.CreateTime = B.DriverCreateTime
WHERE B.DriversLicense is null
OR B.CarRegistration is null
OR B.BizCertificate is null
OR B.Insurance is null;

Solution

Try this:

$query=$this->db->query("SELECT A.*, B.* FROM (SELECT A.* FROM DriversInfo as A, InvoiceQueue as B WHERE A.CreateTime = B.DriverCreateTime AND B.Error <> 1 GROUP BY A.CreateTime UNION  SELECT DISTINCT A.* FROM DriversInfo as A, OrderInfo as B  WHERE A.CreateTime = B.DriverKey  AND B.Invoice <> '0') as A LEFT JOIN DriversDoc as B on A.CreateTime = B.DriverCreateTime WHERE B.DriversLicense is null OR B.CarRegistration is null OR B.BizCertificate is null OR B.Insurance is null;");