Page 1 of 1
complicated select
Posted: Sun Mar 17, 2019 6:30 pm
by Wolfgang
Here a small sample
word book chapter vers
test 1 1 1
me 1 1 1
test 1 1 2
not 1 1 2
me 2 1 1
test 2 1 1
How would a select look like if I need to find all occourences where word="test" and word="me" and book,chapter and verse are the same. Meaning finding all verses where test and me is in it.
In a later select I would ask if book and chapter are the same and vers differs up to three.
Is this possible with one select? And when yes, how?
Re: complicated select
Posted: Sun Mar 17, 2019 9:40 pm
by doublemax
Which database engine do you use? E.g. if it's Sqlite, you should use a simple table structure and leave the full text search to the database:
https://www.sqlite.org/fts5.html
Other SQL databases probably have similar functions.
Re: complicated select
Posted: Mon Mar 18, 2019 7:03 am
by utelle
Wolfgang wrote: ↑Sun Mar 17, 2019 6:30 pm
Here a small sample
word book chapter vers
test 1 1 1
me 1 1 1
test 1 1 2
not 1 1 2
me 2 1 1
test 2 1 1
How would a select look like if I need to find all occourences where word="test" and word="me" and book,chapter and verse are the same. Meaning finding all verses where test and me is in it.
If you just want to search for pairs of words and
if occurences of words are given in a table
words with the structure you described, the answer is rather simple - you just have to join the table with itself and specify appropriate conditions:
Code: Select all
select w1.book, w1.chapter, w1.verse from words w1, words w2
where w1.book=w2.book and w1.chapter=w2.chapter and w1.vers=w2.vers
and w1.word='test' and w2.word='me';
Wolfgang wrote: ↑Sun Mar 17, 2019 6:30 pm
In a later select I would ask if book and chapter are the same and vers differs up to three.
Is this possible with one select? And when yes, how?
This can be accomplished by adjusting the condition for the vers as follows:
Code: Select all
select w1.book, w1.chapter, w1.verse from words w1, words w2
where w1.book=w2.book and w1.chapter=w2.chapter and abs(w1.vers-w2.vers) <= 3
and w1.word='test' and w2.word='me';
You could even parameterize your SQL statement by uying a parameter for the vers distance.
Re: complicated select
Posted: Tue Mar 19, 2019 1:53 pm
by Wolfgang
Is there a way to not show duplicates?
Re: complicated select
Posted: Tue Mar 19, 2019 9:21 pm
by utelle
Wolfgang wrote: ↑Tue Mar 19, 2019 1:53 pm
Is there a way to not show duplicates?
Sure. Just add the key word
distinct, for example:
Code: Select all
select distinct w1.book, w1.chapter, w1.verse from words w1, words w2
where w1.book=w2.book and w1.chapter=w2.chapter and abs(w1.vers-w2.vers) <= 3
and w1.word='test' and w2.word='me';
Re: complicated select
Posted: Thu Mar 28, 2019 8:02 am
by Wolfgang
Thank you