-2

I've a table that has id_categoria field having comma separated value, e.g., 1,2,3,4,64,31,12,14, because a record can belong to multiple categories. If I want to select records that belongs to category 1, I have to run following SQL query

SELECT * 
FROM cme_notizie 
WHERE id_categoria LIKE '1%' 
ORDER BY `id` ASC

and then select all records from the record set that have id_categoria exactly 1 in id_categoria. Let's assume that the value 1 does not exist, but column value like 12, 15, 120 ... still contains 1. There is a way to take only 1? without taking derivatives or other?

Ejaz
  • 8,719
  • 3
  • 34
  • 49
Xanger
  • 67
  • 9
  • 6
    Argh, normalize your database. Don't put multiple values in a single column. – LittleBobbyTables - Au Revoir May 07 '15 at 15:17
  • 1
    Are you saying id_categoria contains a comma-separated string? – Tab Alleman May 07 '15 at 15:18
  • 1
    Here's a question that explains why keeping multiple categories in a column is a bad idea. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad The #1 reason is exactly what you're running into right now. – Andy Lester May 07 '15 at 15:19
  • 1
    So what is the correct method for my needs? – Xanger May 07 '15 at 15:22
  • 3
    You should use an intermediate table containing `notizie` `categoria` association – Sébastien Sevrin May 07 '15 at 15:23
  • 1
    create a new table with 2 columns, one column for `id_item` and the second one for `id_categoria`. Then you can have as many records for a single `id_item` as many categories it belongs to. I think it is called `one to many` relationship – Ejaz May 07 '15 at 15:25
  • Horrible for performance (use integers instead of strings). Please use "M:N" relationship (with additional table). – 18C Aug 04 '19 at 05:24

1 Answers1

1

As comments say, you probably shouldn't do that. Instead, you should have another table with one row per category. But if you decide to go with this inferior solution, you can do the following:

SELECT * 
FROM cme_notizie 
WHERE CONCAT(',', id_categoria, ',') LIKE '%,1,%' 
ORDER BY id ASC
Maxime Lapointe
  • 93
  • 4