How do I change mysql output based on value

Question

For example, if queue_watch shows a 0, I want the actual mysql output to say No, else say Yes. Changing the scheme 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;

The SQL works for what I need, but I like to know if changing the output using SQL rather than PHP is possible. Also, is it possible to group all the queue_names in one row to each user_name so I don't have to have 153 rows returned.

Thanks

"Also, is it possible to group..." Please look up GROUP BY in the manual. You may also want to look at GROUP_CONCAT. And please don't ask two completely unrelated questions in one question. — Mark Byers
– Mark Byers, Commented May 22, 2012 at 20:34
I know sorry for hijacking the two part question but since it was related to the same query I thought might as well. I did think of GROUP BY but the problem is group by returns one user and one queue name, since one user can belong to many queues - so want it in a kind of list, but don't know if its possible — Jonathan Weinraub
– Jonathan Weinraub, Commented May 22, 2012 at 20:58
sorry i figured it out, group by on the bottom, with group_concat in the select did it... — Jonathan Weinraub
– Jonathan Weinraub, Commented May 22, 2012 at 21:11

JHS · Accepted Answer · 2012-05-22 20:12:42Z

10

It not an over head. It is better to be done in the query, I feel.

Try this -

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;

answered May 22, 2012 at 20:12

JHS

7,8812 gold badges34 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Weinraub Over a year ago

This one actually solved what I needed, (I saw it first honestly), and I just added the as to rename the column. Thanks

Mark Byers · Accepted Answer · 2012-05-22 21:03:45Z

6

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

Simple case expression:

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

Searched case expression:

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

Or you could use the MySQL specific IF construct:

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

You may want to consider what should happen if your value is something other than 0 or 1.

edited May 22, 2012 at 21:03

answered May 22, 2012 at 20:12

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

7 Comments

Mark Byers Over a year ago

@MarcusAdams: Sorry I was actually going to add that. I was just was testing my answer in SQL Fiddle just to make sure everything was OK, but to my surprise it didn't work there. Wasn't sure if I had mis-remembered the syntax, mistyped something, or if there was a bug in SQL Fiddle. Seems like it's a bug in SQL Fiddle.

Marcus Adams Over a year ago

Nice job showing the ANSI standard way with CASE as well as more succinct MySQL IF method.

Jonathan Weinraub Over a year ago

I like the case..as method, but where do I put the case, I get sql error no matter where i plop it into the query.

Jake Feasel Over a year ago

@MarkByers The bug is due to the column name having a comma in it. Adding a alias (sans-comma) works around it: sqlfiddle.com/#!2/d41d8/775

Jake Feasel Over a year ago

@MarkByers I've fixed this bug, now. sqlfiddle.com/#!2/d41d8/711 Thanks!

|

Collectives™ on Stack Overflow

How do I change mysql output based on value

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related