sql query with "with and in clause"

i have a table which store user name, hobby and city .hobby field contain different hobby joined using "," operator eg swim开发者_开发知识库ming, basket, cricket. I want to search user name who match at least one hobby according to my search criteria.

You should not have multiple attributes in one column. That's one of the number one rules of 3nf database design. Now you have to figure out ways to parse this data. This issue only gets worse and worse each and every day. Seperate the hobbies as multiple rows in your database.

I agree with @JonH that there shouldn't be more than one piece of information in a column. It stops the row being truly atomic.

But you are where you are, and you can use the LIKE clause to return rows that match a substring within a column.

Something like:

select hobbycolumn from hobbytable where hobbycolumn like '%swimming%'

for example

To do this properly you need to restructure your tables if possible. For what you are looking for a possible way would be to have 3 tables. I'm not sure who the city belongs to, so I put it with the user.

1 for user with the following cols:

• id
• name
• city

A table for for hobbies:

• id
• name

And a user_hobbies join table that allows each user to have multiple hobbies, and each hobby to have multiple users:

• id
• user_id (foreign key)
• hobby_id (foreign key)

Then searching for a user with a certain hobby is:

SELECT user.id, user.name FROM user
INNER JOIN 'user_hobbies' on user_hobbies.user_id=user.id
INNER JOIN 'hobbies' on hobbies.id = user_hobbies.hobby_id
WHERE hobbies.name LIKE "query";