Reputation: 307
In a development environment with SQLite 3.7.11
and Java
, despite having read the following answers:
am finding the usage of the SQLite IN
clause not very straight-forward.
Assume a simple table TEST
with the following structure:
----------------------------
id PRODUCT TAG
(int) (text) (text)
----------------------------
1 Cinthol Soap, Bath, Cleaning
2 Vim Dishwash, Kitchen, Cleaning
3 Burger Food, Breakfast, Lunch, Dinner
The following queries are behaving this way:
----------------------------------------------------------------------------
Query Result Expected
----------------------------------------------------------------------------
SELECT PRODUCT FROM TEST WHERE TAG IN('Soap') Cinthol Cinthol
SELECT PRODUCT FROM TEST WHERE TAG IN('Dinner') <EMPTY> Burger
SELECT PRODUCT FROM TEST WHERE TAG IN('Soap', 'Bath') <EMPTY> Cinthol
SELECT PRODUCT FROM TEST WHERE TAG IN('Cleaning') <EMPTY> Cinthol, Vim
So the questions are:
instr
function)?Furthermore, the TAG
column eventually has to be bound with an array of tokens in Java, building the query dynamically. The answers listed above have pointers to that, though.
Thanks in advance!
Upvotes: 1
Views: 130
Reputation: 1690
In clause doesn't work like this.assume if you had one TAG each column you could get the results.you need to add another table to keep your tags.in this table you need pk , foreign key(id deom tests) ,and tag so that you wil have multitags for each product.this is a solution you can make different.You had better search database notmalization first.gl
Upvotes: 1