0

I am trying to search on search database for user-input string. I would like to show any record that matches one or more input words. I right now have following code/Query:

String sqlStr = "SELECT ID as _id, * FROM Had_Table 
                    WHERE Collection_ID = " + CID + whereClause + " 
                           AND ID IN  (SELECT rowid FROM Had_Virtual_Table
                                         WHERE Had_Virtual_Table MATCH ?
                                       )";

String[] qStr = {query};

Cursor sHadCursor = sHadlistDB.rawQuery(sqlStr, qStr);

This returns results for a string, say, "Fat cat" only if they both exist in a record. I would like to get record even if it has only one of those words.

Also, I want records not be repeated twice(or more) if both words(fact cat) are found in a particular record.

Can anyone suggest anything?

Thank you

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
Abu Qatada
  • 211
  • 3
  • 16
  • [Similar question](https://stackoverflow.com/questions/37516017/exact-phrase-first-before-anything-else-in-sqlite-fts). – CL. Sep 18 '17 at 18:12
  • @AbuQatada Look at the group by clause in sql below is a rough idea here select n, count(*) from table group by n count(*) will be the item you search for and n will be an index – Vector Sep 18 '17 at 21:38
  • @CL This throws error that there is no such column 'rank'. I thought it was created by FTS4 and was hiddent? `SELECT rowid FROM Had_Virtual_Table WHERE Had_Virtual_Table MATCH ? ORDER BY rank ` This is how I created the table: `CREATE VIRTUAL TABLE IF NOT EXISTS Had_Virtual_Table USING FTS4(PlainEng_Text, PlainArab_Text` Anything here I need to change? – Abu Qatada Sep 19 '17 at 04:32
  • @AbuQatada That question generates the rank values manually (`SELECT 1 AS rank` etc.). – CL. Sep 19 '17 at 07:04

0 Answers0