SQLite Complex Query Help

Question

Hi, I have a very complex query going on in SQLite, and I need a bit of help understanding how to do it.

The following example is of my Database:

Category:

CatID | CatTitle
----------------
  1   | XYZ
  2   | Sample


Content:

ItemID | ItemCatID | ItemText | ItemText2 | ItemText3 | ItemText4
-----------------------------------------------------------------
  1    |     1     |   Test   |    Bla    |  Sample   |  MoreContent
  2    |     1     |   Test2  |  BlaBla   |  Sample2  |  Other Content
  3    |     2     |   Test3  |  BlaBla2  |  Sample3  |  Other Content2

Now, I basically want to do a query, on a search string.. (i.e. "%XYZ%" with the wildcards) and I want to search not only the CatTitle, but also ItemText, ItemText2 and ItemText3

The parameters I want to return though are: CatID, CatTitle, ItemID, and if possible, "ItemFilteredText" (which can be anything from ItemText to ItemText4, depending on which is the match)

Heres the thing, If the Query Matches CatTitle, then if it returns ItemID, it should be the FIRST ItemID and NOT the LAST ItemID

I have the following SQL somewhat working...

select DISTINCT Category.CatID,
       Category.CatTitle, 
       Content.ItemID,
       Content.ItemText, 
       Content.ItemText2,
       Content.ItemText3  
from   Content,Category  
where  Category.CatID =  Content.ItemCatID 
       AND ( Category.CatTitle like'%XYZ%' 
             OR Content.ItemText like '%XYZ%' 
             OR Content.ItemText2 like '%XYZ%' 
             OR Content.ItemText3 like '%XYZ%')  
GROUP BY Category.CatTitle ORDER BY Category.CatID ASC

It returns the data... but Grouping By Category.CatTitle means that Content.ItemID would return 2, instead of 1

Any Ideas?

Answer 1

Assuming that you are using integers for content.ItemID and the first result is characterized by a lower ItemID modify the query to include min(Content.ItemID)

select DISTINCT Category.CatID, Category.CatTitle, min(Content.ItemID), Content.ItemText, Content.ItemText2, Content.ItemText3 from Content,Category where Category.CatID = Content.ItemCatID AND (Category.CatTitle like'%XYZ%' OR Content.ItemText like '%XYZ%' OR Content.ItemText2 like '%XYZ%' OR Content.ItemText3 like '%XYZ%') GROUP BY Category.CatTitle ORDER BY Category.CatID ASC

should do the work.