I have problems making a query, maybe someone can help.

What do I have:
A standard select * from 'table_a' where .... query
In this table there is a string which contains a unique number/string
from
table_b, lets call this column "special_string".
This string starts always at position x and this string is followed by
a
":".

I want to join both tables on this substring and "special_string"

Any Ideas how I can acchieve this?

Alexander

On 24-09-10 20:56, Electricdummy wrote:
if you have a table which contains all nummbers from 1 upwards to 1000

and you do
SELECT * from numbers where locate('2', number)-locate('1', number)=1;

than you will get all numbers like:
12
120
121
122
123
123
124
125
126
127
128
129
312
412

it really does not matter much that this is not a JOIN......

--
Luuk

On Fri, 24 Sep 2010 11:56:52 -0700 (PDT), Electricdummy wrote:
If you plan to use this more than once, add a column to hold the
substring, populate it with something like

UPDATE mytable SET mysubcol = SUBSTR(myothercol, 37, 14);

then throw an index onto that column. If you want to use this for a long
time, add a trigger to populate that column every time you insert a row
or update your other column.

--
77. If I have a fit of temporary insanity and decide to give the hero
the chance to reject a job as my trusted lieutentant, I will retain
enough sanity to wait until my current trusted lieutenant is out of
earshot before making the offer. --Anspach's Evil Overlord List