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
Bogdan
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
Mosty mostacho
source
to share
WHERE will not work, but it will.
SELECT
a.f1,
b.f1,
IF(a.f3 <> '', a.f3, b.f3) as fc3
FROM
a
LEFT JOIN b on b.f4 = a.f4
HAVING fc3 = 'something'
i renamed to countable field to avoid potential collision of column names
+3
dweeves
source
to share
Use logic.
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 a.f3 = 'something' OR (a.f3 == '' AND b.f3 = 'something')
+2
Konerak
source
to share