Re: JOIN on multiple items in one column
Hi,
Here's another way to split lists in mysql using the substring_index function and a cross-tab on a table listing the number of levels.
First, add a few more rows to atable provided by Felix. Spaces should not matter if you are comparing integers.
INSERT INTO atable (id, list) VALUES (NULL, '1910, 1904 , 1906');
INSERT INTO atable (id, list) VALUES (NULL, '1875,1910,1902,1903');
select * from atable;
+----+---------------------+
| id | list |
+----+---------------------+
| 1 | 1901,1902,1903 |
| 2 | 1910, 1904 , 1906 |
| 3 | 1875,1910,1902,1903 |
+----+---------------------+
1. Find the maximum length of your delimited list (just the length of the list minus the length with delimiters removed)
select max(length(list)-length(replace(list, ',', ''))+1) as length from atable;
+--------+
| length |
+--------+
| 4 |
+--------+
2. Create a table storing a sequence of numbers up to the max length.
create table level(id tinyint not null primary key);
insert into level values (1),(2),(3), (4);
3. Use cross tab techniques and nested substring index functions to compare each value in the list. This join should be faster (not tested) because it can use an index on the lookup id. Also, you can get the position in the list that matches the lookup table.
SELECT
atable.id,lookup.name, level.id as pos
FROM atable, lookup, level
WHERE substring_index(substring_index(atable.list, ',', level.id), ',', -1)=lookup.id
and length(atable.list)-length(replace(atable.list, ',', ''))+1>=level.id
order by 1;
+----+-----------+-----+
| id | name | pos |
+----+-----------+-----+
| 1 | Bread | 1 |
| 1 | Cheese | 2 |
| 1 | Spam | 3 |
| 2 | Chocolate | 2 |
| 3 | Cheese | 3 |
| 3 | Spam | 4 |
+----+-----------+-----+
If you are confused, this is what the nested substring functions do..
select substring_index(substring_index('1901,1902,1903', ',', 1), ',', -1) as value;
+-------+
| value |
+-------+
| 1901 |
+-------+
select substring_index(substring_index('1901,1902,1903', ',', 2), ',', -1) as value;
+-------+
| value |
+-------+
| 1902 |
+-------+
select substring_index(substring_index('1901,1902,1903', ',', 3), ',', -1) as value;
+-------+
| value |
+-------+
| 1903 |
+-------+
Because the number of records in each list varies, I added the second condition to the where clause. Without it, value 1903 would be checked twice in the first row.
select substring_index(substring_index('1901,1902,1903', ',', 4), ',', -1) as value;
+-------+
| value |
+-------+
| 1903 |
+-------+
Chris