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