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

[diafol]

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.

[diafol]

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

true enough