Jan Bartholdy wrote:
> Dear All,
> I have the following problem:
> The table in a database is organized like this one:
> Sample species_ species_b species_c
> A1 0 5 0
> A2 0 0 3
> A3 1 1 5
>
> I would like to create a selection, which contains only samples,
containing
> a number of a species_a_ species_b or species_c. It should be like this:
>
> Sample A1: 5 species_b
> Sample A2: 3 species_c
> Sample A3: 1 species_a, 1 species_b and 1 species_c
>
> With the "select command" I can't do it. Thanks for suggestions.
> Jan
You can... if you really want to...
If your table is defined as
create table (sample varchar(10), sp_a int, sp_b int, sp_c int);
Values are inserted as
insert into t ('A1',0,5,0),('A2',0,0,3),('A3',1,1,5);
Now a simple select gives you
mysql> select * from t;
+--------+------+------+------+
| sample | sp_a | sp_b | sp_c |
+--------+------+------+------+
| A1 | 0 | 5 | 0 |
| A2 | 0 | 0 | 3 |
| A3 | 1 | 1 | 5 |
+--------+------+------+------+
3 rows in set (0.00 sec)
If you want the output as described, try
select
concat("Sample ",sample,": ",
if(sp_a>0,concat(sp_a," species_a"),""),
if(sp_a>0&&(sp_b>0||sp_c>0),", ",""),
if(sp_b>0,concat(sp_b," species_b"),""),
if(sp_b>0&&sp_c>0,", ",""),
if(sp_c>0,concat(sp_c," species_c"),"")) Species
from t;
And you will get
+--------------------------------------------------+
| Species |
+--------------------------------------------------+
| Sample A1: 5 species_b |
| Sample A2: 3 species_c |
| Sample A3: 1 species_a, 1 species_b, 5 species_c |
+--------------------------------------------------+
3 rows in set (0.00 sec)
That's quiet near, I guess
Frank Busch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
