Sizzling Code Sizzling Code - 3 months ago 37
MySQL Question

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

Answer

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`