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 --discuss@lists.mariadb.org To unsubscribe send an email todiscuss-leave@lists.mariadb.org