Re: lenght variables

[Randy Clamons]

Just a suggestion. What you are trying to do is code a single field with more than one piece of data. Even when you do figure it out, it is going to be extremely inefficient because of indexing issues.

One way you could do this would be using a LIKE comparator in your select. Assuming you know the 'grandfather' id:

Select * from [tablename] WHERE id LIKE '12345_' ORDER BY id;

This statement will not use any index you have attached to the table. If the table is small it's not a real problem, but once your table reaches significant size your query will be slow.

Another way to go might be to create a table of relationships that includes 2 ids from your original table, one column for the 'parent' and one column for the 'child' and possibly a third column that describes the relationship between the to "wife", "child", etc. Maybe one more column for ordinal ('1', '2', etc). Then use a join in your select. Something like this:

SELECT *
FROM people as parent
LEFT OUTER JOIN relations ON relations.parentid = parent.id
LEFT OUTER JOIN people as child on relations.childid = child.id
WHERE parent.id = 12345
ORDER BY relations.type, relations.ordinal;

Assuming you set indices on people.id, relations.parentid and relations.childid this statement would use those indices.

Best of luck!