complicated select Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
Wolfgang
Experienced Solver
Experienced Solver
Posts: 82
Joined: Mon Jan 28, 2019 8:22 am

complicated select

Post by Wolfgang » 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.

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?

User avatar
doublemax
Moderator
Moderator
Posts: 13725
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: complicated select

Post by doublemax » Sun Mar 17, 2019 9:40 pm

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.
Use the source, Luke!

utelle
Moderator
Moderator
Posts: 856
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: complicated select

Post by utelle » Mon Mar 18, 2019 7:03 am

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.

Wolfgang
Experienced Solver
Experienced Solver
Posts: 82
Joined: Mon Jan 28, 2019 8:22 am

Re: complicated select

Post by Wolfgang » Tue Mar 19, 2019 1:53 pm

Is there a way to not show duplicates?

utelle
Moderator
Moderator
Posts: 856
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: complicated select

Post by utelle » Tue Mar 19, 2019 9:21 pm

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';

Wolfgang
Experienced Solver
Experienced Solver
Posts: 82
Joined: Mon Jan 28, 2019 8:22 am

Re: complicated select

Post by Wolfgang » Thu Mar 28, 2019 8:02 am

Thank you

Post Reply