mysql - subquery in IN clause? -

i have 2 tables named as

product_category

create table `product_category` (   `pid` int(11) not null,   `cid` int(11) not null ) engine=innodb default charset=latin1;  -- -- dumping data table `product_category` --  /*!40000 alter table `product_category` disable keys */; insert `product_category` (`pid`,`cid`) values   (1,1),  (2,3),  (3,2),  (4,2),  (5,3),  (1,2),  (2,4),  (3,1); 

and category table

create table `category` (   `id` int(11) not null auto_increment,   `cat_name` varchar(50) not null,   `mapped_cat_id` varchar(250) not null,   primary key (`id`) ) engine=innodb auto_increment=5 default charset=latin1;  -- -- dumping data table `category` --  /*!40000 alter table `category` disable keys */; insert `category` (`id`,`cat_name`,`mapped_cat_id`) values   (1,'c1','1,2,4'),  (2,'c2','2,3'),  (3,'c3','3,4'),  (4,'c4','4,1,3'); /*!40000 alter table `category` enable keys */; 

when run query

select distinct pid product_category cid in (1,2,4) 

i got result of pid (1,3,4,2)

but when run query

select distinct pid product_category cid in (select mapped_cat_id category id=1) 

i got result of pid (1,3)

how use subquery 'in' clause ?

i know way of asking question wrong because dont know how create table here thats why wrote query instead of table.

you might looking find_in_set() function

select distinct pid product_category find_in_set(cid, (select mapped_cat_id category id=1)); 

sample fiddle

since mapped_cat_id saved comma - seperated values(varchar), taking first integer condition checking. here 1