How to do a Join in MySql based on partial matches?
Question by Shyam Natraj Kanagasabapathy
I have two tables.
Table A
Column 1 Column 2
CT 3C 10.5 -23.12
OT 5A 11.2 -24.5
Table B
Column 1 Column 2
PRIM 12.3 -24.51, 10.5 -23.12, 61.24 -78.23
SEC 8.7345 -46.1934, 10.49 -49.1834
TERT 18.98 -28.12, 11.23 -24.78, 1.45 -24.11, 11.2 -24.5
Resulting Table after the join:
Column 1 Column 2 Column 3 Column 4
CT 3C 10.5 -23.12 PRIM 12.3 -24.51, 10.5 -23.12, 61.24 -78.23
OT 5A 11.2 -24.5 TERT 18.98 -28.12, 11.23 -24.78, 1.45 -24.11, 11.2 -24.5
Is there a way to this match without splitting the contents of ‘column 2 in Table B’ into separate columns? The contents in ‘Column 2 of Table B’ are delimited by ‘,’.
10.5 -23.12 should match with 12.3 -24.51, 10.5 -23.12, 61.24 -78.23 as it is contained in the list of values present in it.
I can’t split them into separate columns because in some cases there are as many as 300 distinct values in it separated by a ‘,’
Answer by OMG Ponies
Short Term Solution
Use the FIND_IN_SET function:
SELECT a.column1, a.column2,
b.column1, b.column2
FROM TABLE_A a
JOIN TABLE_B b ON FIND_IN_SET(a.column2, b.column2) > 0
Long Term Solution
Denormalized data should only be stored as a performance improvement, after it’s been determined necessary — not before.
You need a many-to-many table between TABLE_A and TABLE_B to hold the relationships represented in the comma separated lists.
Answer by Starx
300 distinct values This is clearly not a field. A nested table, might be a better name it.
You are doing it wrong. If a single field contains about 300 records, you should keep it as a different table.
