how to select all related items of a product...???

[amith_ami]

hi all pls help me....
im trying to select all related items of a particular product... related items are also another products...
here is an example of my table product...

   prod_id      related_id 
   100          101,102,103
   101          104,105   
   102          101,100
   103          100,101 
   104          103
   105          104

if i select product with prod_id = 100, it should select all related products from the table....
pls help me to fix this....
tnx in advnc...

[pritaeas]

If you are using MySQL then look into FIND_IN_SET(). There's a thread about it.

[amith_ami]

@pritaeas tnx 4 ur reply....
but here i have only one table named tbl_product, prod_id and related_id are also in this table.....
what i'm actually trying to do is... if i select product with prod_id=100 it should show the three related products with the prod_id 101,102,103

[pritaeas]

What have you tried?

[amith_ami]

i tried this in one

SELECT * FROM `tbl_product` WHERE FIND_IN_SET('101,102,103',prod_id)

but result is... MySQL returned an empty result set (i.e. zero rows).

[pritaeas]

Yes, if you look at the manual, you can see that your parameters are reversed.

[amith_ami]

oh sry.... @pritaeas its my mistake... now i changed it to this....

SELECT * FROM `tbl_product` WHERE FIND_IN_SET(prod_id,'101,102,103')

and its working fine now.....
tnx 4 ur reply pritaeas....

Would not this sort of structure be more appropriate?

prod_id      related_id 
   100          101
   100          102
   100          103
   101          104
   101          105   
   102          100
   102          101 
   103          100
   103          101 
   104          103
   105          104

[pritaeas]

Would not this sort of structure be more appropriate?

Yes it would, although you do not always have the luxury of changing the database structure.

you do not always have the luxury of changing the database structure.

true enough