giving a select of strings that only contain strings from another table

Question

I am having difficulty I writing a correct sqlite query to achieve the following.

I have 2 tables: words and sounds

example: content table words:

**wordName**
ape
base
house
step
round
hat
hoot
hot

content table sounds:

**soundName** **soundActive**
     a               1
     b               0
     o               1
     ou              0
     u               1
     s               1
     h               1
     t               1
     e               0
     f               0
     p               1

I would like to write a query that gives me back words that only contain active sounds. Notice ou is not the same as o with u. So in this case the result should be: hat

I came up with the following:

select words.wordName
from words join sounds on words.wordName like '%'||sounds.soundName||'%'
where sounds.soundActive=1
group by words.wordName;

But this gives me also words that have sounds that are not active. As soon as it has one active sound the word is given.

I tried with words.wordName not like '%'||sounds.soundName||'%' where sounds.soundActive=0 group by words.wordName what seemed more logical to me but this gives back all words.

Update: Yohanes Khosiawan second solution works on sqlite.

select words.wordName as wName, avg(sounds.soundActive) as allActive
from words join sounds on words.wordName like '%'||sounds.soundName||'%'
group by words.wordName
having allActive=1;

But to also get the sounds with one/two letters work I will make a new table: example: content table sounds2:

**sound2Name** **sound2Active**
au                    0
ou                    1
oo                    0

The result should be house, hat, hot

Possible duplicate. Please see [this question](http://stackoverflow.com/questions/1930809/mysql-join-query-using-like) — programmer43229, Oct 28 '14 at 11:50
How would it be possible to determine whether `ou` is one or two sounds? — CL., Oct 28 '14 at 12:28
Maybe I could fix this issue by making two tables. 1 for all the letters and one for sounds that have more than 1 letter. — brokenhip, Oct 28 '14 at 12:37

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

It's a pretty tricky problem you have there.
I found that using '%'||sounds.soundName||'%' gave me invalid result, e.g., ape had been joined with b. Since, in here it's explained, that it's treated as or operator.

Thus, to be more reliable, I decided to use regular expression.
Try to check this out:

select words.wordName as wName, avg(sounds.soundActive) as allActive --words which its substring has `0` soundActive value will have average < 1 
from words join sounds on words.wordName REGEXP sounds.soundName{1,} --it means that whether a particular sound occurs at least once in the wordName
group by words.wordName
having allActive=1;

SQLFiddle: http://sqlfiddle.com/#!2/377f70/20

OR, if for SQLite environment (without REGEXP) - edited based on OP's comment -:

select words.wordName as wName, avg(sounds.soundActive) as allActive
from words join sounds on words.wordName like '%'||sounds.soundName||'%'
group by words.wordName
having allActive=1;

SQLFiddle: http://sqlfiddle.com/#!5/377f7/3

edited May 23 '17 at 10:33

Community

1
1

answered Oct 28 '14 at 12:00

Yohanes Khosiawan 许先汉

2,453
4
24
28

Thanks for that. But the problem is that this doesn't work on sqlite as REGEXP is not a function in sqlite – brokenhip Oct 28 '14 at 12:07
are you sure? http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query – Yohanes Khosiawan 许先汉 Oct 28 '14 at 12:09
I've provided an alternative for that. – Yohanes Khosiawan 许先汉 Oct 28 '14 at 12:13
In database browser for SQLite it doesn't work. I also tried in the SQL fiddle link where it also didn't work. Maybe I could add it to database browser for SQLite but this won't make it work on an android mobile – brokenhip Oct 28 '14 at 12:14
1

Thanks! That did it. All I had to do is use || instead of concat as this is also not a function on sqlite. The problem of the other sounds (eg. ou) still exists but I am already a lot further now. – brokenhip Oct 28 '14 at 12:26

Hans Kilian · Answer 2 · 2014-10-28T13:25:34.117

1

To get around the problem with 'ou' matching both 'ou', 'o' and 'u', I'd replace 'ou' with a special character in the sounds table. I used '$'. Then you can replace 'ou' with '$' in your words before joining.

My SQL ended up looking like this:

select words.wordName
from words join sounds 
where replace(words.wordName, 'ou', '$') like '%'||sounds.soundName||'%'
group by words.wordName
having min(soundActive) = '1'

SQLfiddle: http://sqlfiddle.com/#!2/57f72/11

edited Oct 28 '14 at 13:25

answered Oct 28 '14 at 12:06

Hans Kilian

18,948
1
26
35

I think an extra table with all sounds that have more than 1 letter would do it. But again I am stuck. I tried 'select words.wordName, avg(sounds.soundActive), avg(sounds2.soundActive), avg(sounds.soundActive and sounds2.soundActive) from words join sounds, sounds2 on words.wordName like '%'||sounds.soundName||'%' and words.wordName like '%'||sounds2.soundName||'%' group by words.wordName having avg(sounds.soundActive)=1 and avg((sounds.soundActive and sounds2.soundActive)' But clearly this doesn't work. Any idea? – brokenhip Oct 28 '14 at 14:19
I think figuring out what sounds to break a word into is a problem that is too complex for SQL to handle. For instance if you have a 'th' sound, then you'll have problems with the two words 'author' and 'outhouse'. You probably already have a problem with 'ou' but I can't think of a word off the top of my head where you have 'ou' after each other but they should be regarded as separate sounds. – Hans Kilian Oct 28 '14 at 14:51

giving a select of strings that only contain strings from another table

2 Answers2