1

I have a brand_name column in my brand_names table and a product_name column in my product_names table.

At the moment, I have two separate SELECTs (one on brand_names.brand_name and one on product_names.product_name) and I use a UNION to OR the two resultsets together. However, when a search is made for "SomeBrandName Some Product Name", even though such a product exists, my SQL returns zero results (this is because the terms - SomeBrandName Some Product and Name - don't all appear in brand_names.brand_name and they don't all appear in product_names.product_name).

So I need help to work out SQLite / FTS3 equivalent of something like...

SELECT (brand_names.brand_name || ' ' || product_names.product_name) AS brand_and_product_name FROM brand_names, product_names WHERE brand_and_product_name MATCH 'SomeBrandName Some Product Name'

What is the actual SQLite / FTS3 SQL that I need to achieve this?

In terms of research, I have read through the SQLite FTS3 guide but it doesn't mention multiple tables.

I've also seen a similar question which is a bit more advanced and so may well be overkill for the simple search I am trying to achieve here.

Community
  • 1
  • 1
ban-geoengineering
  • 18,324
  • 27
  • 171
  • 253

2 Answers2

1

For that you must have to prepare first virtual tables for both of your tables . Then you can apply indexed search(FTS Search) on them using join same as you use join with simple table .

Hafiz Hashim
  • 344
  • 3
  • 8
0

An FTS search can be done only in FTS indexes.

If you want to have a result for "Brand Product", you have to create an FTS table that contains these words in a single row. (To reduce storage, try using an external content table on a view.)

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for this! I have now shifted the brand_name column into the same table as the product_name column. Do you know how I can do a FTS query over both columns? (Found nothing that helps here - http://www.sqlite.org/fts3.html . – ban-geoengineering Dec 24 '14 at 17:04
  • Thema documentation you linked says that MATCH can take the table name instead of a column name. – CL. Dec 26 '14 at 14:33