how to create query for below table

Question

This is the table of database

**

id  products  free_pro_id
 1  laptop              2
 2  pen-drive           -
 3  hard-disk         2,4
 4  mouse               3

i am just wondering how to create query for below output

product   free_id_pro**
laptop    pen-drive
hard-disk pen-drive,mouse
mouse     hard-disk

You should pair it after a query in some programming language — Flash Thunder, Apr 27 '19 at 08:29
as far as I know, there is no way so split `2,4` to array of values and iterate through it, but even if that would work really slow — Flash Thunder, Apr 27 '19 at 08:36

score 0 · Accepted Answer · answered Apr 27 '19 at 09:13

I understand your problem. But your table implementation is not correct. First, you have to learn about Database Normalization.

Please refer to this link https://www.studytonight.com/dbms/database-normalization.php

You can have a table for products and another table for promotions.

Table Products
In this table you can have the prodId and the prodName

prodId-------prodName
1--------------laptop
2--------------pen_drive
3--------------hard_disk
4--------------mouse

Table Promotion
In this table, you can store what are the free products you are giving when user buys a product.

promoId--prodId----freeProdId
1-------------1------------2
2-------------3------------2
3-------------3------------4
4-------------4------------3

If you want to get the free products given for laptop you can do the following query

SELECT freeProdId from Promotion where prodId = 1;

I have given a very simple query to give you a basic understanding. You can modify the query, play around with it and do many things. (Ex. Get the prodName instead of the id)

Sudeshna · Answer 2 · 2021-02-03T12:29:56.903

This is the table of database arrange this type of product table in database

id ----- products ----- free_pro_id    
1 ------- laptop --------- 2    
2 ------- pen-drive-------- -    
3 ------ hard-disk ------- 2    
3 ------ hard-disk ------- 4
4 ------- mouse ---------- 3

This is the query to get output of above table

SELECT foo.products ,product.products FROM product foo inner join product on foo.id=product.free_pro_id

how to create query for below table

2 Answers2