0

I have a table like the following table.

entityId |  mId      |  supertTypes                     |  SubTypes
------------------------------------------------------------
12       |  /m.098yu | /m.0yhtg, /m.089hj0__            | Null
13       |  /m.kju_c_| /m.0ypwec, /m.0bnm0__, /m.0hgt   | /m.098yu

My query is:

select mId from table where superTypes= /m.0yhtg;

The answer should be /m.098yu

I wrote the following queres based on page1 and page2but I could not get correct result.

select mId from table where supertypes RLIKE "[[:<:]]/m.0yhtg[[:>:]]";

Select mId from table where supertypes LIKE '/m.0yhtg';

How can I solve this problem?

Community
  • 1
  • 1
user3487667
  • 519
  • 5
  • 22
  • Did you try adding wildcards to your `LIKE` statement? `LIKE '%/m.0yhtg%'`? – AdamMc331 Jun 30 '15 at 16:24
  • 1
    Don't store multiple values in a column. Build another table. – Rick James Jun 30 '15 at 22:34
  • @Mcadam331 Yes I tried but it did not work for all kind of values. – user3487667 Jul 02 '15 at 13:26
  • Hmm. Also, I agree with Rick James on this, here is more info: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – AdamMc331 Jul 02 '15 at 13:27
  • @RickJames I got this suggestion from _juergen d_. But I use this table instead of storing my information in a file. Therefore, use multi table is a kind of cheating ;) – user3487667 Jul 02 '15 at 13:31

1 Answers1

1

You can use find_in_set for that but that requires a list without whitespaces. So you need to remove them first with replace

Select mId from table 
where find_in_set('/m.0yhtg', replace(supertypes, ' ', '')) > 0

But you should actually rather change your table design. Never store multiple values in a single column!

A better DB design would be adding a new table

entities table
--------------
entityId
mId


types table
----------------
id
name


entity_types table
------------------
entity_id
type_id
is_super_type

Instead of the field is_super_type you could add another table called entity_subTypes and name the entity_types table entity_supertypes instead.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • each entity can have more than one super type and I am not sure about the number of super type. How can I index them in more than one column? – user3487667 Jun 30 '15 at 15:50