Well not really an answer given the requirement you posted but
you can break the list in the second table in a temporary table
and join that one.
so you will go from
index, list
to
index list[0]
index list[1]
index list[2]
...
I have a stored procedure somewhere to split the list but it is slow to do in
MariaDB but it will be ok if the number of rows is not very big (some
thousands).
For these kind of task I prefer to break to awk or shell in general.
Hope that helps
On 9/13/24 5:09 PM, Gingko via discuss wrote:
Hello,
I would like to know if is there some (efficient!) way to join two tables on a
list of values
contained in (likely a single column) of the joined table, whatever form could
have
this list of values ?
For example, I have a main table containing:
main_table:
+------------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+------------+----------------+
| index | int(11) | NO | PRI | 0 | |
| to_join | int(11) | NO | | 0 | |
| <other fields, irrelevant> | | | | |
+------------+------------------+------+-----+------------+----------------+
… and a secondary table containing:
joined_table:
+------------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+------------+----------------+
| index | int(11) | NO | PRI | | auto_increment |
| int_list | <list_of_ints> | YES | | (empty) | |
| <other fields, irrelevant> | | | | |
+------------+------------------+------+-----+------------+----------------+
We could suppose, here, as a simple test case, that main_table has five rows
where to_join contains respectively 1, 2, 3, 4, and 5,
and the joined_table has a single row with the single column int_list combining
2 and 4
in any form (but of course, this list could actually have any length).
I'd like to join these tables using a statement like:
SELECT
<anything>
FROM main_table AS M
LEFT JOIN joined_table AS J
ON M.to_join <some_operator to> J.int_list;
… in such a way that main_table would be joined to joined_table using any value
contained in int_list, and the statement is expected to give two rows as a
result.
I tried to set int_list as a TINYTEXT containing "2,4" and use:
SELECT
<anything>
FROM main_table AS M
LEFT JOIN joined_table AS J
ON M.to_join IN (J.int_list);
… but this doesn't work, of course, my comma separated list is seen as a whole
single value.
I also tried, still using TINYTEXT, the list as "[2][4]" and use:
SELECT
<anything>
FROM main_table AS M
LEFT JOIN joined_table AS J
ON J.int_listLIKE CONCAT('%[', M.to_join , ']%');
This works (more or less), but it is excruciatingly slow, especially knowing
that main_table
can actually contain millions of rows, and joined_table several thousands.
Is there a better solution for doing that?
As the final goal implies, somewhere later, associating to_join in main_table,
via int_list,
to the index auto increment primary key of the joined_table, using multiple
rows there
is not an option.
Regards,
Gingko
_______________________________________________
discuss mailing list [email protected]
To unsubscribe send an email [email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
