How to get a CSV string for column from all the rows matching a given criteria?

saasira · #1 (**permalink**) 04-25-10, 16:22

Hi,
I have a table named `action` as defined below:

Code:

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`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1

I want to find a combination of the action.name fields as CSV whose sum(action.value)=number.

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.

for example:

Code:

select CSV(a.name) from action a where sum(a.value)=7;

or perhaps using GROUP_CONCAT like:

Code:

select group_concat(a.name) from `action` a having 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.

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,
Samba

it-iss.com · #2 (**permalink**) 04-26-10, 05:27

I think you are missing the GROUP BY clause in your SELECT statement which is causing your problem.

saasira · #3 (**permalink**) 04-26-10, 09:04

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 ;

==> 'view,edit,add'=7

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,

Samba

it-iss.com · #4 (**permalink**) 04-26-10, 10:39

hi Samba,

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.

Ronan

saasira · #5 (**permalink**) 04-26-10, 13:28

Hi Ronan,

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,
Samba

it-iss.com · #6 (**permalink**) 04-26-10, 14:32

Hi Samba,

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech