MySQL SELECT CASE WHEN something THEN returning null
56,405
CASE … WHEN NULL
will never match anything, and CASE NULL
will always match the ELSE
clause (which in your case returns age
, i. e. NULL
).
Use this:
CASE COALESCE(age, 0) WHEN 0 THEN … ELSE age END
Update:
You also need to alias your tables and use the aliases in the field descriptions:
SELECT *,
CASE COALESCE(age, 0)
WHEN '0' THEN
(
SELECT MAX(age)
FROM who wi
JOIN ages ai
ON ai.whoid = wi.wid
WHERE wi.father = w.father
AND wi.mother = w.mother
)
ELSE
age
END AS newage
FROM who w
LEFT JOIN
ages a
ON a.whoid = w.wid
ORDER BY
newage
Comments
-
baturalpdincdari almost 4 years
Table
who
wid--name-------father---mother 1----Daisy------David----Liza 2----Jenny------Joe------Judy 3----Meggy------Mike-----Manuela 4----Sarah------Joe------Judy 5----Chelsea----Bill-----Hillary 6----Cindy------David----Liza 7----Kelly------Joe------Judy
Table
ages
aid---whoid---age 1-----1--------0 2-----2--------0 3-----3-------14 4-----4-------30 5-----5-------22 6-----6-------17 7-----1-------18
I want that list as a result:
id---name------age 1----Meggy-----14 2----Cindy-----17 3----Daisy-----18 (Selected data that bigger than 0) 4----Chelsea---22 5----Sarah-----30 6----Jenny-----30 (Her age is 0 on ages table and Sarah's age with same father and mother) 7----Kelly-----30 (No data on ages table and Sarah's age with same father and mother)
I tried that query:
SELECT *, (CASE age WHEN '0' THEN ( SELECT age FROM ages a LEFT JOIN who w ON w.wid = a.whoid WHERE w.father = father AND w.mother = mother ORDER BY a.age DESC LIMIT 1 ) ELSE age END ) AS newage FROM who LEFT JOIN ages ON wid = whoid ORDER BY newage
What's wrong with that?
-
baturalpdincdari over 13 yearsThank you. This resolved ordering problem, but still have problems with selecting correct age.
-
Quassnoi over 13 years@baturalpdincdari: alias your tables.
-
baturalpdincdari over 13 yearsThat's it. Thank you so much again.