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 [email protected]
To unsubscribe send an email [email protected]
