MySQL WHERE clause on unnormalized table

Question

I want to do a SELECT query on a table to get product infos. One field of the table is "productgroup_ids". A value looks like this: "3||4||13".

This database was not created by me.

So I wrote a Rest service to get the contents in JSON. Now I want products of a specific group e.g. 3 and 4. I get parameter like this: "3,4"

so then the Query should be look like this:

"SELECT name, description FROM products WHERE productgroup_ids in (3,4);"

But then, I have the problem that only the products only with 3 or 4 are in the result.

Can I parse the value of the field with the query, or do I have to read out all products, split a "||", compare, and if it contains the requested group, add it to an array?

score 0 · Answer 1 · edited May 23 '17 at 12:14

i will assume that || are the separators in the database so

first thing: an example of 3 and 13 is better as this example almost lead me down a wrong solution, for info this one: (NOT CORRECT SOLUTION)

"SELECT name, description FROM products WHERE '||'+productgroup_ids+'||' like '%||'+replace('3,4',',','||')+'||%'

but obviously, this would NOT work for 3 and 13

So what now. The obvious answer is that there is no easy way. The solution is to: 1) split the JSON string into a temporary table 2) do a select with a join

To split the string into temp table take a look at this: MySQL Split Comma Separated String Into Temp Table

Lets assume the temp. table is called TempJSONCategory has only one column called category. then do a select like this:

SELECT name, description 
  FROM products 
 WHERE products_primary_key in
       (select products_primary_key 
          from products
          join TempJSONCategory 
            on '||'+products.productgroup_ids+'||' like '%||'+TempJSONCategory.category+'||%')

Tadaaa. Thats it. Yes, its not really pretty, but reasonably fast. But thats the price for having a bad DB design :-/

Edit: Had to edit, select was a bit wrong:

Yeah the problem with 3 and 13 and so on was the reason i asked this question. :D Your second suggestion should be the right thing, but "so much" work for actually nothing. And I cant normalize the tables because somebody else manage the CMS which is using this database... — johnbraum, Apr 13 '15 at 08:28

score 0 · Accepted Answer · answered Apr 14 '15 at 15:55

0

Because of the small amount of the data (not more than 50 data sets) I selected now all products splitted the field with the groups and checked it with the params.

Now it works. But it's still a bad solution.

answered Apr 14 '15 at 15:55

johnbraum

276
4
18

MySQL WHERE clause on unnormalized table

2 Answers2