Codeigniter, Join и Case When 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();

Но я получаю эту ошибку ниже при выполнении вышеуказанного запроса.

 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

Может ли кто-нибудь сказать, что не так в моем запросе и как его решить?

++++++++++++++++++++++++++++++++++++++++++++++++ Обновление:

Удалены отдельные кавычки

 $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;";

Но теперь это ниже ошибки.

 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

Solutions Collecting From Web of "Codeigniter, Join и Case When Query"

использовать псевдоним для упрощения

 $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);

ПРИМЕЧАНИЕ. Обозначьте имя столбца и имя таблицы с помощью ` not with ' а также при ссылке на tablename. имя столбца исключает точку . из \

используйте ` tablename . `column_name

В вашем запросе вы используете '' одиночную квоту» в имени таблицы и имени столбца, с помощью которой она рассматривается как строка, поэтому замените единую квоту тегом “.

Здесь заменен код:

 $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;";

Просто удалите цитаты вокруг имени ваших таблиц.

 SELECT * FROM osp_job_details (...)

И для чего это стоит, вы должны попробовать использовать activerecord для codeigniter. Вам не придется беспокоиться с 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();

И если это не сработает, добавьте скобки вокруг вашего CASE WHEN 😉

Удалите все одинарные кавычки:

 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;

Если вы хотите избежать имен таблиц и полей, которые вы можете использовать как «`

ОБНОВЛЕНИЕ: заменить регистр:

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

с:

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

Привет, просто добавьте это условие в предложение where
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)");