John Siniger John Siniger - 5 months ago 8
SQL Question

Have AND Clause if variable is not null in MySQL Select

Hello I am trying to make an If statement inside SQL Select query, I would like to apply only if variable $department is different than NULL. if the variable is different then Null place

P2.department = P1.department
as AND Clause.

The query line is as follows:

,(SELECT SUM(P2.fee) FROM cases P2 WHERE
IF('$department' IS NULL, 0, P2.department = P1.department) AND curency = 1) as fee_USD


In the current situation the query is working but it displays wrong results. My question is this is the best way to include
P2.department = P1.department AND
inside the query. Any help is welcome.

Here is the full query:

SELECT
P1.id
,P1.department
,P1.curency
,P1.fee
,count(P1.id) as count_cases
,(SELECT SUM(P2.fee) FROM cases P2 WHERE
IF('$department' IS NULL, 0, P2.department = P1.department)
AND curency = 1) as fee_USD

FROM cases P1 WHERE 1";
if (!empty($department)) { $sql .= " AND P1.department = '$department'"; }
if (!empty($status)) { $sql .= " AND P1.status = '$status'"; }

Answer

Seems no need to use IF, just AND will be also well;)

,(SELECT SUM(P2.fee) FROM cases P2 WHERE 
 curency = 1 AND '$department' IS NOT NULL AND P2.department = P1.department) as fee_USD

Edited

,(SELECT SUM(P2.fee) FROM cases P2 WHERE 
 curency = 1 AND ('$department' = '0' OR P2.department = P1.department)) as fee_USD

This query will do P2.department = P1.department only when $department is not 0.