Geek asks and answers

How to change mysql output based on value

For example, if queue_watch shows 0, I want the actual mysql output to say no, otherwise say yes. Changing the schema is not an option.

SELECT user.user_name, queue.queue_name, queue_access.queue_access,
    queue_access.queue_watch
FROM queue_access
INNER JOIN user ON user.user_id = queue_access.user_id
INNER JOIN queue ON queue.queue_id = queue_access.queue_id
WHERE queue_access <> '' OR queue_watch = 1
ORDER BY user_name;

SQL works for what I need, but I like to know if changing output is possible using SQL, not PHP. Also, is it possible to group all the queues on the same line with each username, so I don't need to return 153 lines.

thank

+5
source share
2 answers

It is not over the head. Better to do it in the request, I feel.

Try it -

SELECT user.user_name, queue.queue_name, queue_access.queue_access,
       IF(queue_access.queue_watch = 0, 'No', 'Yes')
FROM queue_access
INNER JOIN user ON user.user_id = queue_access.user_id
INNER JOIN queue ON queue.queue_id = queue_access.queue_id
WHERE queue_access <> '' OR queue_watch = 1
ORDER BY user_name;
+8
source

You can use the standard expression CASE. There are two different ways to write an expression CASE.

:

SELECT
    CASE queue_access.queue_watch
        WHEN 0 THEN 'No'
        ELSE 'Yes'
    END AS your_alias,
    ...

:

SELECT
    CASE WHEN queue_access.queue_watch = 0 THEN 'No' ELSE 'Yes' END AS your_alias,
    ...

MySQL IF:

SELECT
    IF(queue_access.queue_watch, 'Yes', 'No') AS your_alias,
    ...

, , , - 0 1.

+5

More articles:


All Articles