Self join to find the row position

I have a word list table and another table that has all the characters used in each word. For e.g. if there is a word "test" then the word characters table will have 4 rows t,e,s,t.

mysql> select * from word_list;
+---------+---------+
| word_id | word    |
+---------+---------+
|       1 | This    |
|       2 | is      |
|       3 | test    |
|       4 | message |
|       5 | for     |
+---------+---------+
5 rows in set (0.00 sec)

mysql> select * from word_chars;
+----+---------+----------+-----------+
| id | word_id | char_seq | word_char |
+----+---------+----------+-----------+
|  1 |       1 |        1 | T         |
|  2 |       1 |        2 | h         |
...
...

| 19 |       5 |        2 | o         |
| 20 |       5 |        3 | r         |
+----+---------+----------+-----------+
20 rows in set (0.00 sec)

It is easy to find the word where "e" is at second position. But how do I find the words where "e" is at second position and "a" is at fifth position? There can be seve开发者_如何学JAVAral (upto 8) such conditions.

select word from word_list as a inner join word_chars as b on a.word_id = b.word_id 
where word_char = 'e' and char_seq = '2';
+---------+
| word    |
+---------+
| test    |
| message |
+---------+
2 rows in set (0.00 sec)

Here is are the relevant tables.

drop table if exists word_list;
create table word_list (word_id int not null auto_increment, word varchar(255), primary key (word_id)) ENGINE=InnoDB;
insert into word_list (word) values ('This'), ('is'), ('test'), ('message'), ('for');

drop table if exists word_chars;
create table word_chars (id int not null auto_increment, word_id int, char_seq int, word_char varchar(50), primary key (id), unique key `word_seq` (word_id, char_seq), foreign key (word_id) references word_list(word_id)) ENGINE=InnoDB;
insert into word_chars (word_id, char_seq, word_char) values (1, 1, 'T'), (1, 2, 'h'), (1, 3, 'i'), (1, 4, 's'), (2, 1, 'i'), (2, 2, 's'), (3, 1, 't'), (3, 2, 'e'), (3, 3, 's'), (3, 4, 't'), (4, 1, 'm'), (4, 2, 'e'), (4, 3, 's'), (4, 4, 's'), (4, 5, 'a'), (4, 6, 'g'), (4, 7, 'e'), (5, 1, 'f'), (5, 2, 'o'), (5, 3, 'r')

Update:

Is it possible to return the count or max(char_seq) in the same query? So for ex. In the following answer, it should look like...

+---------+
| word    | count
+---------+
| message |  7
+---------+

select word from word_list as a
inner join word_chars as b on a.word_id = b.word_id
inner join word_chars as c on a.word_id = c.word_id
where b.word_char = 'e' and b.char_seq = '2'
and   c.word_char = 'a' and c.char_seq = '5';

Result:

+---------+
| word    |
+---------+
| message |
+---------+

Can't immediately think of an elegant way to handle any number of possible conditions - maybe someone else has an idea for that.

For any number of conditions (and even any number of matches consisting of conditions) create table conditions ( id, match_id, position, char ) and use query:

SELECT a.word, d.matched FROM
       ( SELECT b.word_id, count(b.id) as matched FROM word_chars b 
       JOIN conditions c 
       ON c.position = b.char_seq
       AND c.char = b.word_char
       WHERE c.match_id = 1
       GROUP BY  b.word_id ) d
JOIN word_list a
ON a.word_id = d.word_id

to return number of matched characters in a word for a given set of conditions with the same match_id (1 here).

+---------+---------+
| word    | matched |
+---------+---------+
| test    |       1 |
| message |       2 |
+---------+---------+

If I had to search the word_list table as it is without having to use the chars table I would do so like this.

select word from 
word_list 
where Substring(word,2,1) = 'e'
and Substring(word,5,1) = 'a'