1

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:

  • Except the first query, why are the others not producing the expected results? Is there something fundamentally wrong in the understanding?
  • If wrong, what is the right query to get the expected results (without using the 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!

Community
  • 1
  • 1
Narayana J
  • 307
  • 5
  • 17
  • 2
    the best solution is database normalization ... also all expected value should be `empty` – Selvin Nov 18 '15 at 10:54
  • proof that should be empty: http://sqlfiddle.com/#!7/0c8a60/2 – Selvin Nov 18 '15 at 11:03
  • `IN` sees if the thing on the left is contained in the list on the right; not if one of the "components" of the thing on the left is listed on the right. – TripeHound Nov 18 '15 at 11:24
  • Dear @Selvin, if you notice the [Answer 1](http://stackoverflow.com/questions/7418849/in-clause-and-placeholders) above, the expected results seem truly reasonable. `Normalization` is a motherhood statement, unfortunately, does not help. Me. Thanks a ton for responding :) – Narayana J Nov 18 '15 at 11:28
  • *Normalization is a motherhood statement* ... then buy some book or take a lesson about "databse normalization" ... without this knowladge it is hard to build good (or any) apps that using database ... – Selvin Nov 18 '15 at 11:31
  • Yes, @TripeHound. At least it is apparent now. I am at a loss for workarounds or proper ways to solve it. – Narayana J Nov 18 '15 at 11:33
  • You need a second table for the tags having (probably) the `ID` column and a `TAG` (singular) column. This would have one entry for each tag for each ID (i.e. "1, 'Soap'", "1, 'Bath'" and "1, 'Cleaning'") and then you need to `JOIN` the two tables. – TripeHound Nov 18 '15 at 11:37
  • also *the expected results seem truly reasonable* ... no, for all who knows SQL basics the only reasonable results is empy – Selvin Nov 18 '15 at 11:38
  • 1
    without normalization, the only way you can search by tags, wich is a TERRIBLE performance way, is by using SELECT PRODUCT FROM TEST WHERE TAG LIKE '%Soap%', and you would have to separate it by Pipes on both side to search like '%|Soap|%' preventing it to get a tag like Soap2. – avjr Nov 18 '15 at 11:59
  • @Selvin, admittedly, SQL has never been my forte, sir! – Narayana J Nov 18 '15 at 12:08
  • 1
    @NarayanaJ, the "IN" clause works exactly the opposite way of what you are doing. Here is a short example "SELECT * FROM test WHERE PRODUCT IN ('Cinthol', 'Vim');" should give you the first 2 rows. So it means: here is a list of values for this column, give me all the rows whose value in this column matches at least one of these values. – Samuil Yanovski Nov 18 '15 at 12:27
  • 1
    [Now you can use Answer 1...](http://sqlfiddle.com/#!7/671f1/4) ... but next time, please learn some basics, before asking ... also I'm not getting why this question has +2 ... It not deserve upvotes – Selvin Nov 18 '15 at 12:34
  • @Selvin, thanks indeed! Love the way you troll 'lazy' noobs ;-) – Narayana J Nov 18 '15 at 13:06

1 Answers1

1

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

Burak Karasoy
  • 1,682
  • 2
  • 21
  • 33
  • Ok guys, will spend some time understanding `normalization` and return hopefully with a solution. However, please look at [Answer 1](http://stackoverflow.com/questions/7418849/in-clause-and-placeholders). Its an accepted answer and it beats me as to why my queries, which are on very similar lines, fail. Thanks both! – Narayana J Nov 18 '15 at 11:42
  • 1
    @NarayanaJ IN requires a single value on the left side. – CL. Nov 18 '15 at 11:44