Search code examples
sqlsqliteandroid-sqlite

sql query returns no result with many to many relation

I'd like to start with explaining what's my db looks like. I have the item table which is in relation many to many with two other tables tag and keyword. 

table: item
columns: id, name, subcategory, category

table: item_tag_join
columns: itemId, tagId

table: tag
columns: id, name

table: item_keyword_join
columns: itemId, keywordId

table: keyword
columns: id, name

Now, what I want to do is I want to search through the keyword.name, tag.name, item.name, item.category and item.subcategory and return the item from item table, when any of the columns contain expected string.

With my query I manage to get the expected results as long as there're some records in the join tables. However when I have only one entry in my database, in the item table then the query returns empty.

i.e. I have added "Ferrari" to the item and I want to search "rar" it comes back with no results. Im working with sqllite3 and here's the query I need Your help with:

SELECT DISTINCT item.* 
from item, tag, keyword 
LEFT JOIN item_tag_join ON 
item.id = item_tag_join.item_id
LEFT JOIN item_keyword_join ON item.id = item_keyword_join.item_id
WHERE UPPER(item.name) LIKE UPPER('%' || "cab"  || '%') 
or UPPER(item.category) LIKE UPPER('%' || "cab" || '%') 
or UPPER(item.sub_category) LIKE UPPER('%' || "cab" || '%') 
or (tag.id = item_tag_join.tag_id and UPPER(tag.name) LIKE UPPER('%' || "cab" || '%')) 
or (keyword.id = item_keyword_join.keyword_id and UPPER(keyword.name) LIKE UPPER('%' || "cab" || '%'));

Cheers.

Solution

  • Got it solved by breaking the statements into three separate selects and aplying UNION operator https://www.w3schools.com/sql/sql_union.asp