CODEWITHSUNDEEP
javaandroidsqlite
Narayana J
Narayana J

Reputation: 307

Using the IN clause with SQLite

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:

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

Answers (1)

Burak Karasoy
Burak Karasoy

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

Related Questions