Reputation: 6080

Codeigniter, Join and Case When Query

Trying to Execute this query.

$sql ="SELECT '*' FROM
            'osp_job_details'
                LEFT JOIN
            'osp_job_status_track' ON 'osp_job_status_track'.'JobID' = 'osp_job_details'.'JobID'
                LEFT JOIN
            'osp_job_status' ON 'osp_job_status'.'StatusID' = 'osp_job_status_track'.'StatusID'
                LEFT JOIN
            'osp_job_sub_status' ON 'osp_job_sub_status'.'SubStatusID' = 'osp_job_status_track'.'SubStatusID'
                LEFT JOIN
            'hr_employee_details' ON 'hr_employee_details'.'EmployeeID' = 'osp_job_details'.'AssignToEmployeeID'
                LEFT JOIN
            'osp_job_type' ON 'osp_job_type'.'JobTypeID' = 'osp_job_details'.'JobtypeID'
            WHERE 'isDefault' = 0 AND

            CASE WHEN 'osp_job_status'.'StatusID' = '2'
            THEN  'osp_job_sub_status'.'CurrentStatus' = '3'
            ELSE 'osp_job_status'.'StatusID' >= '2'
                END;";

        $query = $this->db->query($sql);
        return $query->result();

But i am getting this below error on running the above query.

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''osp_job_details' LEFT JOIN 'osp_job_status_track'' at line 2

SELECT '*' FROM 'osp_job_details' LEFT JOIN 'osp_job_status_track' ON 'osp_job_status_track'.'JobID' = 'osp_job_details'.'JobID' LEFT JOIN 'osp_job_status' ON 'osp_job_status'.'StatusID' = 'osp_job_status_track'.'StatusID' LEFT JOIN 'osp_job_sub_status' ON 'osp_job_sub_status'.'SubStatusID' = 'osp_job_status_track'.'SubStatusID' LEFT JOIN 'hr_employee_details' ON 'hr_employee_details'.'EmployeeID' = 'osp_job_details'.'AssignToEmployeeID' LEFT JOIN 'osp_job_type' ON 'osp_job_type'.'JobTypeID' = 'osp_job_details'.'JobtypeID' WHERE 'isDefault' = 0 AND CASE WHEN 'osp_job_status'.'StatusID' = '2' THEN 'osp_job_sub_status'.'CurrentStatus' = '3' ELSE 'osp_job_status'.'StatusID' >= '2' END;

Filename: C:\xampp\htdocs\projects\zorkif_new\system\database\DB_driver.php

Line Number: 330

Can Anyone tell what is wrong in my query and how to solve it?

++++++++++++++++++++++++++++++++++++++++++++++++ Update:

Removed the Single Quotes

$sql ="SELECT * FROM
    osp_job_details
        LEFT JOIN
    osp_job_status_track ON osp_job_status_track.JobID = osp_job_details.JobID
        LEFT JOIN
    osp_job_status ON osp_job_status.StatusID = osp_job_status_track.StatusID
        LEFT JOIN
    osp_job_sub_status ON osp_job_sub_status.SubStatusID = osp_job_status_track.SubStatusID
        LEFT JOIN
    hr_employee_details ON hr_employee_details.EmployeeID = osp_job_details.AssignToEmployeeID
        LEFT JOIN
    osp_job_type ON osp_job_type.JobTypeID = osp_job_details.JobtypeID
    WHERE isDefault = 0 AND

    CASE WHEN osp_job_status.StatusID = 2
    THEN  osp_job_sub_status.CurrentStatus = 3
    ELSE osp_job_status.StatusID >= 2
        END;";

But getting this below error now..

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'isDefault` = 1 AND CASE WHEN `osp_job_status`.`StatusID` = 2 THEN `osp_job' at line 8

SELECT `osp_job_details`.*, `osp_job_type`.`JobTypeName`, `status`, `Substatus`, `osp_job_status`.`StatusID`, `osp_job_sub_status`.`SubStatusID`, `FirstName`, `MiddleNames`, `LastName`, `hr_employee_details`.`EmployeeID`, `osp_job_status_track`.`StatusTrackID` FROM (`osp_job_details`) LEFT JOIN `osp_job_status_track` ON `osp_job_status_track`.`JobID` = `osp_job_details`.`JobID` LEFT JOIN `osp_job_status` ON `osp_job_status`.`StatusID` = `osp_job_status_track`.`StatusID` LEFT JOIN `osp_job_sub_status` ON `osp_job_sub_status`.`SubStatusID` = `osp_job_status_track`.`SubStatusID` LEFT JOIN `hr_employee_details` ON `hr_employee_details`.`EmployeeID` = `osp_job_details`.`AssignToEmployeeID` LEFT JOIN `osp_job_type` ON `osp_job_type`.`JobTypeID` = `osp_job_details`.`JobtypeID` WHERE ` ` isDefault` = 1 AND CASE WHEN `osp_job_status`.`StatusID` = 2 THEN `osp_job_sub_status`.`CurrentStatus` = 3 ELSE `osp_job_status`.`StatusID` >= 2 END ;

Filename: C:\xampp\htdocs\projects\zorkif_new\system\database\DB_driver.php

Line Number: 330

Upvotes: 0

Answers (6)

Joao Ricardo Grapilha Lopes

Reputation: 3

this is an example of what I did to solve a problem in my system that needed a case, and the codeigniter inserted the ` in the query and returned an error. My solution was to put the parentheses => () in the case.

$this->db->select("(case when c.my_column THEN c.my_column ELSE c.other_column END as my_column_name)")

Upvotes: 0

Ashish Pathak

Reputation: 824

Hi Simply Add This Condition in your where clause
CORE QUERY

(CASE
        WHEN
            tbl_account.account_type = 4
        THEN
            1
   ELSE
     `tbl_acc_company`.`acc_comp_status` = 'ACTIVE'
   END
)

CI QUERY

$this->db->where("(CASE WHEN tbl_account.account_type = 4 THEN  1 ELSE tbl_acc_company.acc_comp_status = 'ACTIVE' END)");

Upvotes: 0

xkeshav

Reputation: 54052

use alias to simplify

$sql ="SELECT [specific column names ] FROM `osp_job_details` jd
    LEFT JOIN
            `osp_job_status_track` jst ON (`jst`.`JobID` = `jd`.`JobID`)
    LEFT JOIN
            `osp_job_status` js ON (`js`.`StatusID` = `jst`.`StatusID`)
    LEFT JOIN
            `osp_job_sub_status` jss ON (`jss`.`SubStatusID` = `jst`.`SubStatusID`)
    LEFT JOIN
            `hr_employee_details` hed ON (`hed`.`EmployeeID` = `jd`.`AssignToEmployeeID`)
    LEFT JOIN
            `osp_job_type` jt ON (`jt`.`JobTypeID` = `jd`.`JobtypeID`)

    WHERE `isDefault` = '0' 
     AND CASE 
                WHEN `js`.`StatusID` = '2' THEN  `jss`.`CurrentStatus` = '3'
                ELSE `jd`.`StatusID` >= '2'
            END ";
  $this->db->query($qry);

NOTE: wrap column name and table name with ` not with ' and also when referencing tablename. column name exclude the dot . from \

use `tablename`.`column_name`

Upvotes: 1

Stephan

Reputation: 8090

Remove all the single quotes :

SELECT * FROM
            osp_job_details
                LEFT JOIN
            osp_job_status_track ON osp_job_status_track.JobID = osp_job_details.JobID
                LEFT JOIN
            osp_job_status ON osp_job_status.StatusID = osp_job_status_track.StatusID
                LEFT JOIN
            osp_job_sub_status ON osp_job_sub_status.SubStatusID = osp_job_status_track.SubStatusID
                LEFT JOIN
            hr_employee_details ON hr_employee_details.EmployeeID = osp_job_details.AssignToEmployeeID
                LEFT JOIN
            osp_job_type ON osp_job_type.JobTypeID = osp_job_details.JobtypeID
            WHERE isDefault = 0 AND

            CASE WHEN osp_job_status.StatusID = 2
            THEN  osp_job_sub_status.CurrentStatus = 3
            ELSE osp_job_status.StatusID >= 2
                END;

If you want to escape the table names and fields you can use like "`"

UPDATE: replace the case :

CASE WHEN osp_job_status.StatusID = 2
THEN  osp_job_sub_status.CurrentStatus = 3
ELSE osp_job_status.StatusID >= 2
    END;

with:

(
    (
        osp_job_status.StatusID = 2
        AND
        osp_job_sub_status.CurrentStatus = 3
    )
    OR
    osp_job_status.StatusID >= 2
)

Upvotes: 1

Olivier Coilland

Reputation: 3096

Just remove the quotes around your tables name.

SELECT *
FROM osp_job_details
(...)

And for what it's worth, you should try using activerecord for codeigniter. You won't have to worry with the SQL:

$this->db
    ->from('osp_job_details')
    ->join('osp_job_status_track', 'osp_job_status_track.JobID = osp_job_details.JobID', 'left')
    ->join('osp_job_status', 'osp_job_status.StatusID = osp_job_status_track.StatusID', 'left')
    ->join('osp_job_sub_status', 'osp_job_sub_status.SubStatusID = osp_job_status_track.SubStatusID', 'left')
    ->join('hr_employee_details', 'hr_employee_details.EmployeeID = osp_job_details.AssignToEmployeeID', 'left')
    ->join('osp_job_type', 'osp_job_type.JobTypeID = osp_job_details.JobtypeID', 'left')
    ->where('isDefault', 0)
    ->where("osp_job_status.StatusID = '2' AND osp_job_sub_status.CurrentStatus = '3' OR osp_job_status.StatusID >= '2'")
    ->result();

And if it doesn't work, add brackets around your CASE WHEN ;)

Upvotes: 1

user2334807

Reputation:

In your query you are using '' single quota in table name and column name by which it is treated as a string so replace the single quota with the `` tag.

Here is replaced code:

$sql ="SELECT * FROM
            `osp_job_details`
                LEFT JOIN
            `osp_job_status_track` ON `osp_job_status_track`.`JobID` = `osp_job_details`.`JobID`
                LEFT JOIN
            `osp_job_status` ON `osp_job_status`.`StatusID` = `osp_job_status_track`.`StatusID`
                LEFT JOIN
            `osp_job_sub_status` ON `osp_job_sub_status`.`SubStatusID` = `osp_job_status_track`.`SubStatusID`
                LEFT JOIN
            `hr_employee_details` ON `hr_employee_details`.`EmployeeID` = `osp_job_details`.`AssignToEmployeeID`
                LEFT JOIN
            `osp_job_type` ON `osp_job_type`.`JobTypeID` = `osp_job_details`.`JobtypeID`
            WHERE `isDefault` = 0 AND

            CASE WHEN `osp_job_status`.`StatusID` = '2'
            THEN  `osp_job_sub_status`.`CurrentStatus` = '3'
            ELSE `osp_job_status`.`StatusID` >= '2'
                END;";

Upvotes: 1

Codeigniter, Join and Case When Query

Answers (6)

Related Questions