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?
complicated select Topic is solved
-
- Moderator
- Posts: 19164
- Joined: Fri Apr 21, 2006 8:03 pm
- Location: $FCE2
Re: complicated select
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.
Other SQL databases probably have similar functions.
Use the source, Luke!
-
- Moderator
- Posts: 1130
- Joined: Tue Jul 05, 2005 10:00 pm
- Location: Cologne, Germany
Re: complicated select
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: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.
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';
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';
-
- I live to help wx-kind
- Posts: 184
- Joined: Mon Jan 28, 2019 8:22 am
Re: complicated select
Is there a way to not show duplicates?
-
- Moderator
- Posts: 1130
- Joined: Tue Jul 05, 2005 10:00 pm
- Location: Cologne, Germany
Re: complicated select
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';
-
- I live to help wx-kind
- Posts: 184
- Joined: Mon Jan 28, 2019 8:22 am
Re: complicated select
Thank you