How do I select a row in MySQL that contains multiple values?

Question

I have a MySQL table that looks like this:

Table: Designer
  id: integer  
  name: String
  gallery: string

The gallery row can contain a string value like 23,36,45

Now I want to do a query similar to this:

SELECT * FROM Designer WHERE gallery = '36'

I know I kan use LIKE, but that is not precices enough. That could return both 36 and 136.

I also know I could create another table which links designer and gallery. But since this is not going to be a huge table, I'm adding the foreign gallery ID Key to the gallery row. And I'm lazy right now ;)

So how can I select a row that has the number 36?

UPDATE
Ok, since I'm getting nailed for poor design (yes I know it was), I See the obvious now.

A designer can have many galleries, but a gallery can only belong to one designer. Therefore I only need to add designer ID as a foreign key to the gallery table.

Simple. But not always logical when it's 3AM and you've been workign for 15 hours ;)

Answer 1

If you have to do that you have poorly designed your tables.

One designer can have got many galleries and a gallery belong to one designer means you must create a foreign key 'designer' in your 'gallery' table, and your request will be

SELECT *
FROM Designer
INNER JOIN Gallery
ON Gallery.id = 36
AND Designer.id = Gallery.designer

Answer 2

You really shouldn't store your data like that since it makes queries horribly inefficient. For that particular use case, you can (if you don't care about performance) use:

select * from designer
    where gallery like '36,%'
       or gallery like '%,36'
       or gallery like '%,36,%'
       or gallery = '36'

This will alleviate your concerns about partial matches since something like 136 will not match any of those conditions but 36 in any position will match.

---

However, despite your protestations to the contrary, what you should do is re-engineer your schema, something like:

designer:
    id             integer primary key
    name           varchar(whatever)
designer_galeries:
    designer_id    integer foreign key references designer(id)
    gallery        string
    primary key    (designer_id,gallery)

Then your queries will be blindingly fast. One of the first things you should loearn is to always design your schema for third normal form. You can revert to other forms for performance once you understand the trade-offs (and know how to mitigate problems) but it's rarely necessary unless you database is horribly convoluted.

Answer 3

You can use regular expressions in your WHERE clause instead.

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

SELECT * FROM Designer WHERE gallery REGEXP "(,|^)(36)(,|$)"

Answer 4

I also agree that this is poorly designed table structure but here is the answer

SELECT * FROM Designer where FIND_IN_SET(36,gallery)