Well, to be fair, you don't really have to change the existing tables, at all.
You will get better performance if you do. That is if you convert your child table to:
but you *can* do that on the fly, in the query, if you have no control over the existing data.
id subid text
1.1.2 a This is child text belonging to 1.1.2
1.1.2 b This is child text belonging to 1.1.2
1.1.2 c This is child text belonging to 1.1.2
1.1.3 a This is another child row
You could do:
But that SUBSTR function will mean that you won't be able to take advantage of any index on child_table.id and, of course, you pay the penalty of the extra time needed to use SUBSTR.
SELECT pt.text, c.text
FROM parent_table pt, child_table c
WHERE pt.id = SUBSTR( c.id, 1, LENGTH(pt.id) )
ORDER BY pt.id, c.id
It's not a good idea. It's just something you'd do if you were desperate.