Sql Statement construction

Question

Hi im trying to use Sql within MySql to sort some values and this is what I have:

Select from my table the rows where the value in column status equals online and the value in column tags equals museum, order them by name and in ascending order.

SELECT *
  FROM tech
 WHERE `STATUS` = 'online'
   AND `TAGS` = 'museum'
ORDER BY NAME ASC

However what I need is this:

Select from my table the rows where the value in column status equals online and there is this value in column tags that equals museum, order them by name and in ascending order.

What I means is that right now in my DB my column tags only has one value per row, not very useful for using as tags. I want to change it so that the value in it would instead of "museum" be "museum 3d programming" and SQL check for a particular "word" in that string value.

Can it be done?

Answer 1

Sounds like you want to use LIKE:

SELECT * 
    FROM tech 
    WHERE `STATUS` = 'online' 
        AND `TAGS` LIKE '%museum%' 
    ORDER BY NAME ASC

Answer 2

Try

SELECT *
  FROM tech
 WHERE `STATUS` = 'online'
   AND `TAGS` like '%museum%'
ORDER BY NAME ASC

Answer 3

You just have to use LIKE and %. See this site.

So something like this

..AND `TAGS` like '%museum%'...

Answer 4

`Try this:

SELECT *
  FROM Tech
 WHERE `STATUS` = 'online'
   AND `TAGS` LIKE '%Museum%'
ORDER BY Name ASC

Answer 5

Rather than creating multiple tags in a single column, you should consider splitting the tags off into another table. This is known as database normalization. It will make working with your data much easier, and will avoid having to perform LIKE searches, which can be very slow.

I answered a very similar question not so long ago, and included a sample table structure, some data, and some example queries. The example given has three tables: item, item_tag and tag. item contains, well, items, and tag contains tags. item_tag is a junction table. It helps create a many-to-many relationship between the other two tables: each item may be associated many tags, and each tag may be associated with many items. The junction table sits between the two, and contains a list of item-tag pairs:

Will this (normalised) database structure permit me to search by tags as I intend?

You should also have a look at this MySQL tutorial:

An Introduction to Database Normalization