How to insert multiple id in one row?

[Bravat]

I have table related where i need to store all items that are related to the specific one. I know how to create multiple rows (for example item:id1 is related to item:id2, item:id1 is related to item:id3, item:id1 is related to item:id4 and so one ). How can store all that in one row (item:id1 is related to item:id2,item:id3....) and later be able to use this for query?

[TeNDoLLA]

I think best would be to create two tables and relations between them. Something like this.

 

items

----------

item_id

item_name

 

 

related_items

----------------

item_id

item_name

related_id (references to the table items and field item_id)

 

 

So item in table items can have multiple child items in table related_items. Then you can easily get all the related items for certain item with using a JOIN in your query. Not sure thought if I understood correctly what you are after.

 

[Bravat]

That is the way I made table. I want to put more then one item_id in single row, and latter to be able to use stored item_ids for query. For example:

item_id:1 is related with item_id2, item_id3, item_id4... (I don't know how to do this).

Latter I want to do something like this:

SELECT * FROM item WHERE item_id = (and here I insert values for related_id)

[mannerheim]

I think you can do something like:

 

SELECT * FROM item WHERE item_id IN (SELECT related_id FROM  related_items)

 

or

 

SELECT * FROM item WHERE item_id IN ('1', '2', '3', [..])

[trq]

I want to put more then one item_id in single row

 

That is a poor design decision. You should listen to TeNDoLLA's advice.

[Bravat]

I am standing confused 

I don't want to have situation where I have a 100 rows for one item (id1 is related to id2(one row), id2-id3 (second row) and so on). In that case i will have one big table (for example if I have 1000 items, that are related with 100 other items that will be 100,000 rows). I want to put 100 related items in  one row, and that is puzzling me.

[TeNDoLLA]

100 000 rows is not much in a table if the database and queries are built in right way and optimized. Other choice would be that you have a relations table that links the products together with their id.

 

items

-----------

id

item

 

 

relations

-----------

item_id (references to items (id) )

related_id (references also to items (id) )

 

This way you have only one table with items, and every item appears only one time. The other table is just connecting the relations between these items.

[AbraCadaver]

I will join with TeNDoLLA so hopefully you will listen and save yourself lots of headache later.

[Bravat]

Ok, I will go that way. Thanks for advice