Posted by Mattias Thorslund on 04/07/05 00:26

Rory Browne wrote:

>Hi
>
>I appreciate this is an SQL issue, more than a PHP issue, but since it
>isn't a DB specific issue, and I'm planning on using it with PHP(and
>I'm not subscribed to any SQL lists), I thought I'd post it here.
>
>I have a table a bit like the following:
>
>create table thing (name char(10), value boolean, name_if_true
>char(10), name_if_false(10))
>
>Basicly what will be modified will be the value field. name_if_true,
>and name_if_false are static(and will in practice be in a different
>table). The table may look a bit like the following:
>
>| Accept | 0 | Yes | No |
>| Gender | 1 | Female | Male
>| State | 0 | Broken |fixed
>
>Can anyone suggest a query that will return:
>
>| Accept | No
>| Gender | Female
>| State | fixed
>
>Preferably in a db independent manner.
>
>
>

Look into the CASE statement. You can use it in the SELECT part of your
query. MySQL also has an IF statement, but I believe that's not
supported by other DBMS's.

Something like:

SELECT
name,
CASE value
WHEN 1 THEN name_if_true
WHEN 0 THEN name_if_false
ELSE 'unknown' END AS result
FROM thing;

/Mattias

[Back to original message]