[sqlite] looking up records with terms in a table

2013-03-26 Thread Gert Van Assche 
All,

I don't know if I can do this.

I have a table T1 with Sentences and a table T2 with Terms.
If I want to find all Sentences with the term GM I search like this:
SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE '%GM%';

What I would like to do is look for all terms that appear in the Terms table.
Something like this (but of course this does not work):
SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);

So my question is: can this be done, and if so, how?

thanks

Gert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Clemens Ladisch 
Gert Van Assche wrote:
 What I would like to do is look for all terms that appear in the Terms table.
 Something like this (but of course this does not work):
 SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Gert Van Assche 
Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

gert


2013/3/26 Clemens Ladisch clem...@ladisch.de:
 Gert Van Assche wrote:
 What I would like to do is look for all terms that appear in the Terms table.
 Something like this (but of course this does not work):
 SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);

 SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms


 Regards,
 Clemens
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Rob Richardson 
I think you need wildcards:

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%

RobR, not guaranteeing correct syntax

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Gert Van Assche
Sent: Tuesday, March 26, 2013 12:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] looking up records with terms in a table

Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

gert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Gert Van Assche 
Rob, no that does not work either.
Thanks for trying though.

2013/3/26 Rob Richardson rdrichard...@rad-con.com:
 I think you need wildcards:

 SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%

 RobR, not guaranteeing correct syntax

 -Original Message-
 From: sqlite-users-boun...@sqlite.org 
 [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
 Sent: Tuesday, March 26, 2013 12:53 PM
 To: General Discussion of SQLite Database
 Subject: Re: [sqlite] looking up records with terms in a table

 Clemens, doesn't seem to work...

 The terms are just a part of the sentence, not a full match.
 Your query does find full matches.

 thanks

 gert
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread James K. Lowden 
On Tue, 26 Mar 2013 17:14:57 +0100
Gert Van Assche ger...@datamundi.be wrote:

 SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE '%GM%';
 
 What I would like to do is look for all terms that appear in the
 Terms table. Something like this (but of course this does not work):
 SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms
 FROM T2);

SELECT Sentences FROM T1 
WHERE EXISTS (
select 1 from T2
where T1.Sentences like '%' || Terms || '%'
);

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Clemens Ladisch 
Gert Van Assche wrote:
 2013/3/26 Clemens Ladisch clem...@ladisch.de:
 Gert Van Assche wrote:
 What I would like to do is look for all terms that appear in the Terms 
 table.
 Something like this (but of course this does not work):
 SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);

 SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms

 Clemens, doesn't seem to work...

 The terms are just a part of the sentence, not a full match.
 Your query does find full matches.

That's how you wrote it in your original query...

Just add wildcards around the terms:

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE '%' || T2.Terms || '%'


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Gert Van Assche 
Bingo!
Thanks you all!

2013/3/26 Clemens Ladisch clem...@ladisch.de:
 Gert Van Assche wrote:
 2013/3/26 Clemens Ladisch clem...@ladisch.de:
 Gert Van Assche wrote:
 What I would like to do is look for all terms that appear in the Terms 
 table.
 Something like this (but of course this does not work):
 SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE (SELECT Terms FROM T2);

 SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE T2.Terms

 Clemens, doesn't seem to work...

 The terms are just a part of the sentence, not a full match.
 Your query does find full matches.

 That's how you wrote it in your original query...

 Just add wildcards around the terms:

 SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE '%' || T2.Terms || '%'


 Regards,
 Clemens
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Paul Mathieu 
Try this:
SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE
CONCAT('%',T2.Terms,'%')

Paul

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson
Sent: Tuesday, March 26, 2013 10:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] looking up records with terms in a table

I think you need wildcards:

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%

RobR, not guaranteeing correct syntax

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
Sent: Tuesday, March 26, 2013 12:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] looking up records with terms in a table

Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

gert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Petite Abeille 

On Mar 26, 2013, at 10:36 PM, Paul Mathieu pmath...@intellimed.com wrote:

 SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE
 CONCAT('%',T2.Terms,'%')

Alternatively, use FTS [1]:

sqlite create virtual table  sentence using fts4( content text );
sqlite insert into sentence values( 'FTS3 and FTS4 are nearly identical' );
sqlite create table term( content text );
sqlite insert into term values( 'nearly' );
sqlite select * from sentence join term on sentence.content match 
term.content; 
FTS3 and FTS4 are nearly identical|nearly

[1] http://www.sqlite.org/fts3.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

2013-03-26 Thread Gert Van Assche 
Nice

2013/3/26 Petite Abeille petite.abei...@gmail.com:

 On Mar 26, 2013, at 10:36 PM, Paul Mathieu pmath...@intellimed.com wrote:

 SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE
 CONCAT('%',T2.Terms,'%')

 Alternatively, use FTS [1]:

 sqlite create virtual table  sentence using fts4( content text );
 sqlite insert into sentence values( 'FTS3 and FTS4 are nearly identical' );
 sqlite create table term( content text );
 sqlite insert into term values( 'nearly' );
 sqlite select * from sentence join term on sentence.content match 
 term.content;
 FTS3 and FTS4 are nearly identical|nearly

 [1] http://www.sqlite.org/fts3.html

 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users