var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: How to get a CSV string for a column from all the rows matching a given criteria?
I have a table named `action` as defined below:
I want to find a combination of the action.name fields as CSV whose sum(action.value)=number.
CREATE TABLE `action` (
`id` TINYINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` CHAR(12) NOT NULL,
`description` CHAR(250) NOT NULL,
`value` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `value` (`value`),
UNIQUE INDEX `name` (`name`)
what I'm trying to achieve is not actually a group by since there are no common records to group; I want the combination of name column as CSV from records whose sum(value column) = some number.
or perhaps using GROUP_CONCAT like:
select CSV(a.name) from action a where sum(a.value)=7;
would result in 'view,edit,add'; provided the records with value of view=1, edit=2 and add=4 were present in action table.
select group_concat(a.name) from `action` a having sum(a.value)=7;
I know the above is not a valid SQL query but locgically that statement represents what I'm expecting.
Can you folks guide me in geting above result?
Thanks and Regards,
Last edited by saasira; 04-25-10 at
Reason: in order to hint the users that I have also tried GROUP_CONCAT function to achieve the result but to no avail
I think you are missing the GROUP BY clause in your SELECT statement which is causing your problem.
Last edited by it-iss.com; 04-26-10 at
Reason: Misunderstood original post.
Thanks Ronan for your suggestion but the problem is not about having a group by, the problem is that there are no common columns to group;
Here is the data in the table :
|Name : Value|
view : 1
edit : 2
add : 4
delete : 8
Now I want to find the combination of names that make up value=7
i.e. view+edit+add=1+2+4 ;
I doubt if this can be done using group by, but please let me know if that can be achieved by group by or some other means.
Thanks and Regards,
It might be possible to solve this using cartesian product but if your table is large I would not recommend it and solve the problem programatically using recursion.
That sounds interesting; can you give an sample query on the `action` table that I mentioned at the beginning of this post?
This table will have very little data, at most 100 rows; however, it needs to be participating in a join with another table that may contain a few thousands of rows.
I think I can go with cartesian product over this table if things work the expected way.
Thanks in advance,
100 rows could already be too much. I thought it would be max 10 rows. Cartesian product on 100 rows and on the number of possible rows could result in millions of accesses. For instance, 100 rows for every combination of view, add, edit and delete is more or less 100,000,000 hits i.e. 100*100*100*100.
I think that you would be better off doing this programmatically using recursion. This would be better as all information would be stored in memory which can be accessed more quickly.
Tags for this Thread