Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    8

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

    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
    Last edited by saasira; 04-25-10 at 17:37. Reason: in order to hint the users that I have also tried GROUP_CONCAT function to achieve the result but to no avail

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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 06:29. Reason: Misunderstood original post.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    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

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jun 2009
    Posts
    8
    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

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •