SQLite 3, FTS3 - Join Queries

Question

I had gone through the literature available on net to understand the various JOIN for SQLite available in internet. But not able to figure out one that serves my requirement.

I need to perform Filter operation based on various categories and on top I need to perform SEARCH operation from Filtered content.

"TABLE 1: FOOTWARE_MASTER (SQLite TABLE)  

Id = Numeric Primary Key
Item_Type = One of the following (Shoe, sandals, floaters)
Brand = Nike, Puma, Reebok
Description = .. .. .. . .. .. (1000 words)"   

TABLE 2: FOOTWARE_DESCRIPTION (SQLite Virtual Table powered by FTS)  

Id = Numeric Primary Key
IdReferenced = Foreign key (FOOTWARE_MASTER Primary key)
Description  

User selects brand Nike and Item_type = SportShoes User get result set of 1000 items. Now he wants to further minimize them by 'SEARCHING' specific details in description.

i.e. I need to construct query which first FILTER out the dataset and SEARCH for specific text with in that dataset (One alternative, I can write simple JOIN query which can give me appropriate result, but my mail concern is performance). Please suggest specific type of JOIN query which can result in Good Performance.

Answer 1

SELECT *
FROM Footware_Master
WHERE Brand = 'Nike'
  AND Item_Type = 'SportShoes'
  AND Id IN (SELECT IdReferenced
             FROM Footware_Description
             WHERE Description MATCH 'details')