SELECT on values of a comma sep. list

polarbear · April 20th, 2008, 04:57 AM

Hi,

as background for the following discussion, please consider this simple table:

Code:

CREATE TABLE `t1` (
  `id` int(10),
  `values` varchar(50),
);

with this sample content:

Code:

*****************
* id * values   *
*****************
* 1  * 10,20,30 *
* 2  * 5,8,3    *
* 3  * 1,2,3    *
* 4  * 7,8,2    *
*****************

Here's the statement I'd like to execute:

Code:

SELECT * FROM t1 WHERE id IN 
(SELECT values FROM t1 WHERE id = 3)

The subquery returns '1,2,3' which will be casted to 1, I guess. Therefore the outer query returns the record for id=1.

I'm looking for a solution returning the records for id=1, 2 and 3 as contained in the return of the subquery ('1,2,3')

Is it possible to achieve this?

thanks in advance
best regards
polarbear

ciderpunx · May 13th, 2008, 08:46 AM

Its possible, but almost never a good idea to do this in mysql.

If you possibly can, use another table to store your values field as seperate rows. Then you just have a join to do. I.e.

Code:

mysql> CREATE TABLE t2 (id INT PRIMARY KEY AUTO_INCREMENT, t1id INT, val INT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (null, 3, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (null, 3, 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (null, 3, 3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----+----------+
| id | values   |
+----+----------+
|  1 | 10,20,30 | 
|  2 | 5,8,3    | 
|  3 | 1,2,3    | 
|  4 | 7,8,2    | 
+----+----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+----+------+------+
| id | t1id | val  |
+----+------+------+
|  1 |    3 |    1 | 
|  2 |    3 |    2 | 
|  3 |    3 |    3 | 
+----+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1,t2 WHERE t1id=3 AND t1.id=t2.val;
+----+----------+----+------+------+
| id | values   | id | t1id | val  |
+----+----------+----+------+------+
|  1 | 10,20,30 |  1 |    3 |    1 | 
|  2 | 5,8,3    |  2 |    3 |    2 | 
|  3 | 1,2,3    |  3 |    3 |    3 | 
+----+----------+----+------+------+
3 rows in set (0.00 sec)

mysql>

If you don't have the option of doing that, use code to split the fields, eg php explode function or perl's split.

If you can't do any of the above then you can use mysql like the following (assuming you always have three comma seperated fields). This, as well as being ugly will be slow on large datasets as you're doing three subqueries along with your normal query.

Yeuch!

Code:

mysql> SELECT * FROM t1 WHERE `id` NOT IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`values`,',',1),',',-1) FROM t1 WHERE `id`=3) OR `id` IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`values`,',',2),',',-1) FROM t1 WHERE `id`=3) OR `id` IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`values`,',',3),',',-1) FROM t1 WHERE `id`=3);
+----+--------+
| id | values |
+----+--------+
|  2 | 5,8,3  | 
|  3 | 1,2,3  | 
|  4 | 7,8,2  | 
+----+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE `id` NOT IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`values`,',',1),',',-1) FROM t1 WHERE `id`=3) AND `id` NOT IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`values`,',',2),',',-1) FROM t1 WHERE `id`=3) AND `id` NOT IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`values`,',',3),',',-1) FROM t1 WHERE `id`=3);
+----+--------+
| id | values |
+----+--------+
|  4 | 7,8,2  | 
+----+--------+
1 row in set (0.00 sec)

mysql>

HTH
Charlie

--
Charlie Harvey's website - linux, perl, java, anarchism and punk rock: http://charlieharvey.org.uk