-3

I am using MySQL. I am getting two field values A,B as number. I need to create field value C as comma separated values from A to B.

For example, I have three columns A,B and c. A value is '1'. B vales is '5'. I need to get C value as '1,2,3,4,5' in sql query.

Actually, I want to get the "stopped machine report" from multiple table. I have stored the values as from_machine = 1 and to_machine = 5 (Its my mistake). But there is no machine name as 3. If I get count as (to-from-1) it gives 5; But the answer should be 4. That's why I need. If I get this, I could check as IN or NOT IN('1','2','3','4','5').

Please any one give me a solution. Thanks in advance.

user3016421
  • 113
  • 1
  • 12
  • 1
    What have you already tried? – BenM Sep 29 '14 at 09:12
  • 2
    Please [read this](http://stackoverflow.com/q/3653462/1446005) before you go any further down this road. Make sure you really want to do this. And indeed, SQL is not the best tool for string manipulation. – RandomSeed Sep 29 '14 at 09:14
  • You can do it with programming why don't you create that type of values for C column ? – Gopal00005 Sep 29 '14 at 09:18

1 Answers1

2

The answer to your original question can be found in this sqlfiddle

/*sample data*/
create table foo (a int, b int);

insert into foo values (1, 5);

create table numbers (n int);

insert into numbers  values 
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);


/*query*/
select
f.a, f.b, group_concat(n.n)
from
foo f
join numbers n on n.n between f.a and f.b
group by a, b;

/*result*/
| A | B | GROUP_CONCAT(N.N) |
|---|---|-------------------|
| 1 | 5 |         1,2,3,4,5 |

But in fact, you don't need this. Just do the join like I did in above example with BETWEEN. Don't query with IN(...) (at least in this case).

To get the missing ones, you can do a left join and where your_column is null. Have a look at this post, if you don't know what I mean.

fancyPants
  • 49,071
  • 32
  • 84
  • 94