Can I apply a condition in a where clause on a field that I have selected via IF?
I'll give you an example because it's hard to understand what I'm talking about:
SELECT a.f1,
b.f1,
IF(a.f3 <> '', a.f3, b.f3) as f3
FROM a
LEFT JOIN b
ON b.f4 = a.f4
WHERE f3 = 'something'
Now this won't work because I cannot have an alias as a field in the where clause, so how can I apply a condition to that f3 field which is either from a or b. Preferably not putting the IFs bundle in the where clause
+3
source to share
3 answers
Try the following:
SELECT * FROM (
SELECT a.f1, b.f1,
IF(a.f3 <> '', a.f3, b.f3) as f3
FROM a
LEFT JOIN b on b.f4 = a.f4
) S
WHERE S.f3 = 'something'
This is actually less efficient than replacing the value with the formula itself, as it would require a full scan. To improve performance (but decrease readability), you can use this query:
SELECT a.f1 aF1, b.f1 bF1,
IF(a.f3 <> '', a.f3, b.f3) as f3
FROM a
LEFT JOIN b on b.f4 = a.f4
WHERE IF(a.f3 <> '', a.f3, b.f3) = 'something'
+1
source to share