I have table product
id | Name | catid |
1 | A | 2
2 | B | 2
3 | c | 2
4 | D | 2
5 | ch | 2
7 | kj | 2
where id is primary key of product table
Above id get reopen multiple time
reopen table :
re_id | testName | catid | product_id |
1 | not okk | 2 | 1,2,3,4 |
2 | not okk | 2 | 5,7 |
where re_id is primary key
I want .. can i insert multiple value ( how i have shown 1,2,3,4) so that i can get list with WHERE IN condition.
Want to know its safe to use such condition in DB
How i can insert multiple id in single field. and what should be data type
Update :
Below is possible ?
"INSERT INTO reopen (testname,catid,product_id) VALUES ('not okk','2','1,2,3,4') "
AND
"SELECT * FROM reopen WHERE product_id IN (1,2,3,4)"
If you don't want to use native support of referental integrity, you may store a list of values in a varchar field.
In my oppinion it is much better to (follow canonical way) create one more table for multiple links between reopen.id and product.id.
reopen_id | product_id
----------+-----------
1 | 1
1 | 2
1 | 3
1 | 4
2 | 5
2 | 7
By adding composite key on both fields you make duplicate links impossible. To insers data you may use multiple-row insert command INSERRT INTO links (1,1),(1,2)…
For WHERE-conditions you may use normal IN operator
You do not need any text manipulations and it makes queries faster
There is no any limitation to a number of values in the list.
Storage is more optimal in this case
UPDATE:
See sqlfiddle
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments