You could do it with a UNION:
mysql> SELECT 'key0 ' AS 'header'
-> FROM id_key WHERE id = '10' AND key0 != ''
-> UNION
-> SELECT 'key1 '
-> FROM id_key WHERE id = '10' AND key1 != ''
-> UNION
-> SELECT 'key2 '
-> FROM id_key WHERE id = '10' AND key2 != ''
-> UNION
-> SELECT 'other1'
-> FROM id_key WHERE id = '10' AND other1 != ''
-> UNION
-> SELECT 'other2'
-> FROM id_key WHERE id = '10' AND other2 != ''
-> ;
+--------+
| header |
+--------+
| key0 |
| other1 |
+--------+
2 rows in set (0.00 sec)
Awfully clumsy, though. I'd recommend that you restructure
the table like so:
CREATE TABLE `id_key_2` (
`id` int(11) NOT NULL default '0',
`type` enum('key0','key1','key2','other1','other2') NOT NULL default
'key0',
`data` text NOT NULL,
KEY `id_type` (`id`,`type`)
) TYPE=MyISAM
You'll save a ton of space, and your queries will probably
be easier to write and run faster than using multiple
passes with UNION. Plus, you'll be able to add more keys
later without having to completely rebuild the table.
____________________________________________________________
Eamon Daly
----- Original Message -----
From: "Jeremy McEntire" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 07, 2004 11:24 AM
Subject: How to select field names?
> Let key represent the field name.
> Let value reference the data at the 'current' key.
>
> Suppose we have a sample table:
>
> +----+------+------+------+--------+--------+
> | id | key0 | key1 | key2 | other1 | other2 |
> +----+------+------+------+--------+--------+
> | 0 | data | none | none | data | none |
> | 1 | none | none | data | none | data |
> | 10 | data | none | none | data | none |
> | 11 | data | data | none | data | data |
> +----+------+------+------+--------+--------+
>
> How does one:
>
> SELECT key as header FROM table WHERE id = '10' AND value = 'data';
>
> And get:
>
> +--------+
> | header |
> +--------+
> | key0 |
> | other1 |
> +--------+
>
> Further, would it be possible to:
>
> SELECT key as header FROM table WHERE id = '11' AND value = 'data' AND key
=
> 'key_';
>
> To achieve a list of those field names from the table where the row's id
is
> '11', the key is key{something}, and the data held in the cell
corresponding
> to that row and key is 'data'?
>
> Or, in the example above, the results:
>
> +--------+
> | header |
> +--------+
> | key0 |
> | key1 |
> +--------+
>
> Thanks,
> Jeremy
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
