php - Find Duplicate in many to many relation -
i have mysql database table maps many many relation. database table contains 2 columns, foreign keys other tables. database table exists assigning foreign key table foreign key table b.
the structure of table
column 1: conditionid (int11) (foreign key) column 2: packageid (int11) (foreign key)
create table `many_to_many_table` ( `conditionid` int(11) not null, `packageid` int(11) not null ) engine=innodb default charset=latin1; alter table `many_to_many_table` add key `conditionid` (`conditionid`,`packageid`);
i want find out, if combination in table exists. example user has conditions ids 1, 2 , 3. these ids package id 1 exists. entries 1 | 1, 1 | 2 , 1 | 3.
sample data
insert `many_to_many_table` (`conditionid`, `packageid`) values (1, 195), (2, 195), (3, 195), (4, 197), (5, 197), (5, 209), (6, 198), (6, 211), (6, 219), (6, 220);
with sample data query should return packageid 195.
i tried following:
select packageid, conditionid, ( select count(*) many_to_many_table conditionid in (1,2,3) ) rowcount many_to_many_table group packageid having conditionid in (1,2,3) , rowcount = 3
this worked fine in first moment. selects entries have conditionid 1 or conditionid 2 , different packageid. query not exact enough duplicates.
is possible 1 query?
if understand correctly want:
select packageid many_to_many_table conditionid in (1,2,3) group packageid having sum(conditionid = 1) > 0 , sum(conditionid = 2) > 0 , sum(conditionid = 3) > 0;
Comments
Post a Comment