• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Mysql CASE to select a column instead of another if null

Hi all, i have a table that keeps labels for another table. i have structured like that:
fieldname (varchar50), fieldvalue (varchar50), label (text).

The idea is that i can join on that table and select the labels i want to display as table headers. This works perfectly.

But i want to select directly the column value if there's no label in the other table for that field at that value.

I'm using the following query:
SELECT t1.asa, count( t1.asepsis ) AS conteggio, t1.asepsis, l2.label AS lblasepsis, l1.label,
CASE l1.label
WHEN NULL
THEN t1.asa
ELSE l1.label
END AS lblasa
FROM dati AS t1
LEFT JOIN labels AS l1 ON l1.fieldvalue = t1.asa
AND l1.fieldname = 'asa'
LEFT JOIN labels AS l2 ON l2.fieldvalue = t1.asepsis
AND l2.fieldname = 'asepsis'
GROUP BY asa, asepsis

Open in new window

0
ienaxxx
Asked:
ienaxxx
  • 3
1 Solution
 
johanntagleCommented:
Looks like IFNULL is the simplest solution for you: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_ifnull

SELECT ifnull(l1.label, t1.asa)
0
 
ienaxxxAuthor Commented:
Thanks,

it works. But...


... this is the result:


      lblasa
[BLOB - 1B]
BLOB - 1B]
[BLOB - 1B]
[BLOB - 1B]
[BLOB - 1B]


so if i choose to display blobs in the options i get the result.

Ho can i cast a varchar?
0
 
ienaxxxAuthor Commented:
i tried this:

SELECT t1.asa, count( t1.asepsis ) AS conteggio, t1.asepsis, l2.label AS lblasepsis, CONVERT(IFNULL(l1.label, t1.asa), "VARCHAR(50)")

FROM dati AS t1
LEFT JOIN labels AS l1 ON l1.fieldvalue = t1.asa
AND l1.fieldname = 'asa'
LEFT JOIN labels AS l2 ON l2.fieldvalue = t1.asepsis
AND l2.fieldname = 'asepsis'
GROUP BY asa, asepsis

Open in new window



and this:



SELECT t1.asa, count( t1.asepsis ) AS conteggio, t1.asepsis, l2.label AS lblasepsis, CAST(IFNULL(l1.label, t1.asa), VARCHAR(50))

FROM dati AS t1
LEFT JOIN labels AS l1 ON l1.fieldvalue = t1.asa
AND l1.fieldname = 'asa'
LEFT JOIN labels AS l2 ON l2.fieldvalue = t1.asepsis
AND l2.fieldname = 'asepsis'
GROUP BY asa, asepsis

Open in new window



But didn't work.
0
 
ienaxxxAuthor Commented:
Nevermind,  i found that i cannot use VARCHAR in CONVERT function, only CHAR.

Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now