I have to create a table in the way shown below. Can we create in this way? (If Yes)
Table_name: Sample
product_id| product_name| category | 1 | Sample1 | 1|2|3 | 2 | sample2 | 4|5|6 | where category filed which contains multiple values.
& how we can search for category 4 comes in which row of the table.
You cannot create nested table. And the thing on your mind is not a good idea to design table like that. You should have two tables (exactly three which holds the description if the category). One is for the product and the second table holds the category for each product. Example design would look like this,
CREATE TABLE Product ( ProductID INT PRIMARY KEY, ProductName VARCHAR(50) UNIQUE ); CREATE TABLE Category ( CategoryID INT PRIMARY KEY, CategoryName VARCHAR(50) UNIQUE ); CREATE TABLE Product_Category ( RecordD INT AUTO_INCREMENT PRIMARY KEY, CategoryID INT, ProductID INT, CONSTRAINT tb_uq UNIQUE(CategoryID, ProductID) ); and Populate Sample Records
INSERT Category VALUES (1, 'Fruit'); INSERT Category VALUES (2, 'Vegetable'); INSERT Product VALUES (1, 'Apple'); INSERT Product VALUES (2, 'Banana'); INSERT Product VALUES (3, 'Cabbage'); INSERT Product VALUES (4, 'Squash'); INSERT Product VALUES (5, 'Tomato'); INSERT Product_Category (CategoryID, ProductID) VALUES (1,1); INSERT Product_Category (CategoryID, ProductID) VALUES (1,2); INSERT Product_Category (CategoryID, ProductID) VALUES (2,3); INSERT Product_Category (CategoryID, ProductID) VALUES (2,4); INSERT Product_Category (CategoryID, ProductID) VALUES (1,5); INSERT Product_Category (CategoryID, ProductID) VALUES (2,5); sample queries
-- NORMAL QUERY SELECT a.ProductName, c.CategoryName FROM Product a INNER JOIN Product_category b ON a.ProductID = b.ProductID INNER JOIN Category c ON b.CategoryID = c.CategoryID ORDER BY ProductName; -- If you want catgoryName to be comma separated SELECT a.ProductName, GROUP_CONCAT(c.CategoryName) CategoryList FROM Product a INNER JOIN Product_category b ON a.ProductID = b.ProductID INNER JOIN Category c ON b.CategoryID = c.CategoryID GROUP BY ProductName ORDER BY ProductName; 
There seems to be one-to-many relationship between product and category and so you should normalize the category details into a different table as follows:
Then your product_categories table would be
product_id | category_id 1 | 1 1 | 2 1 | 3 2 | 4 2 | 5 2 | 6 Then a simple select statement JOINING the product table with product_categories table will give you all products in a given category.
Make a separate table for categories:
table_categories
product_id| category | 1 | 1 | 1 | 2 | 1 | 3 | 2 | 4 | 2 | 5 | 2 | 6 | Then you can search like this:
SELECT p.product_id, p.product_name FROM table_products p JOIN table_categories c ON p.product_id = c.product_id WHERE c.category = 4 Assuming those are strings in the form of <item>|<item>|<item>, you can, but it would be a better idea to give each category its own row relating to product_id and product_name. That way you can get better performance and have a more 'natural' structure so that you wouldn't have to do queries like:
SELECT * FROM Sample WHERE category LIKE '%4|%' OR category LIKE '%|4|%' or category LIKE '%|4%' in order to retrieve the data. A slightly better solution would be to surround all fields with pipes - if you have the ability to modify these strings beforehand, creating fields in this manner will make dealing with cases where 4 appears in the middle or at the beginning the same (e.g. |4|5|6| could be queried the same way as |3|4|5).
A more natural structure would be something like:
product_id| product_name| category | 1 | Sample1 | 1 | 1 | Sample1 | 2 | 1 | Sample1 | 3 | 2 | sample2 | 4 | 2 | sample2 | 5 | 2 | sample2 | 6 | 
4 happens to be first? the WHERE clause should be WHERE category LIKE '4|%' OR category LIKE '%|4|%' OR category LIKE '%|4'.You need another table to represent the many-to-many relation. You should not insert multiple values into one column.
CREATE TABLE products (product_id, product_name) CREATE TABLE product_category (product_id, category_id) INSERT INTO products (product_id, product_name) VALUES (1, 'Sample1'); INSERT INTO products (product_id, product_name) VALUES (2, 'Sample2'); INSERT INTO product_category (product_id, category_id) VALUES (1, 1); INSERT INTO product_category (product_id, category_id) VALUES (1, 2); INSERT INTO product_category (product_id, category_id) VALUES (1, 3); INSERT INTO product_category (product_id, category_id) VALUES (2, 4); INSERT INTO product_category (product_id, category_id) VALUES (2, 5); INSERT INTO product_category (product_id, category_id) VALUES (2, 6); To retrieve all products in category 4:
SELECT products.* FROM products INNER JOIN product_category ON products.product_id = product_category.product_id WHERE product_category.category_id = 4; Yes you can, in fact we do this by making 'category' (as an example) a BLOB, and thus allowing the ability to store very large subsets of data on one row. This allows a single connection and one query, which is far more optimized.
The only way to process it then - would be to actually do so on the 'Client Side'. You may not want to use LIKE if the data in the field becomes to much.
I think you should create two tables;
Table one
product_id| product_name| 1 | Sample1 | 2 | sample2 | Table two
category_id| product_id| category | 1 | 1 | 1 | 2 | 1 | 2 | 3 | 1 | 3 | 4 | 2 | 4 | 5 | 2 | 5 | 6 | 2 | 6 | 