MySQL Syntax Help

Ignition
1

Hello,

Can someone guide me on using If Else statement in where clause in MySQL query?

This is what I want to do:

distinct(op.Name), ro.PredictedValue, ro.TargetValue, sops.ActualValue
FROM outputcomponents oc
JOIN runoutputs ro ON ro.SubComponentID = oc.ID
JOIN outputs op ON op.ID = oc.OutputID
JOIN sendoutputs sops ON ro.SubComponentID = sops.SubComponentID
WHERE (
IF (SELECT count(SendDataID) from sendoutputs where RunID = 257) > 0)
THEN (ro.RunID = 257 and sops.SendDataID in (SELECT max(SendDataID) from sendoutputs where RunID = 257))
ELSE (ro.RunID = 257)
)
Order by oc.OutputID

But I get a MySQL syntax error when I try that. What is the correct syntax to add If Then Else condition in Where statement?

Thanks,

Shreyas

2

Hi Shreyas,

IF functions in MySQL are like Excel:

IF(expression_to_test, expression_if_true, expression_if_false)

So yours would be something like:

IF ((SELECT count(SendDataID) from sendoutputs where RunID = 257) > 0), (ro.RunID = 257 and sops.SendDataID in (SELECT max(SendDataID) from sendoutputs where RunID = 257)), (ro.RunID = 257))

Don’t know if using an IF in a WHERE clause is the best way to go or not, but sometimes it can’t be helped. :slight_smile: