say i have 2 tables. a column on one of these tables is a CSV string, stored as varchar (ie. 'apple,banana,lemon'). I want to join a second table to it, using something like:
select * from table1 t1
LEFT JOIN table2 t2
ON (t1.col IN t2.col)
using my fruit example, if a given row from table1 has a value of apple, banana or lemon, that row should join to the row in table2.
can something like this work? labkey isnt throwing a SQL error, but it also isnt joining properly. i realize that having data in a CSV string is not very good, but in this situation it's much easier for the user to maintain it, rather than using a mapping table or something.
thanks. |
| |
| Matthew Bellew responded: |
2011-06-07 13:45 |
IN is not a set method not a string method. So this won't work. You could try using LIKE
t2.col LIKE ('%' || t1.col || '%')
This has a few problems
- first
'apple,banana,lemon,grapefruit' LIKE '%grapefruit%' is TRUE
but
'apple,banana,lemon,grapefruit' LIKE '%grape%' is also TRUE
- second
this is effectively a cross-product join and can get very, very expensive |
| |
|
|