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

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_list LIKE 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 to discuss-leave@lists.mariadb.org