Add Multiple Values In One Column

September 26, 2022 Post a Comment

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

Solution 1:

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);

SQLFiddle Demo (with records included)

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;

Solution 2:

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:

product: product_id, product_name.
product_categories: product_id, category_id

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.

Solution 3:

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

Solution 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       |

Solution 5:

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;

Lab Sql