How can I partially match two tables in a many-to-many situation in MySQL? -

currently i'm trying develop system send automated replies customers based on attributes select tickets send in.

for instance if send in ticket product_id of 10 , reason_id of return 5. want able send answer matches these criteria in partial way. in previous question requested in matching options of ticket criteria exactly. if there 5 criteria answer ticket had fulfill these 5 criteria exactly, no more, no less.

what want accomplish partial match these tickets. if customer sends in ticket product_ids 10,20,30,40 , reason_id 5 want able match answer has @ least options e.g. answer product_ids 10,20 , reason_id 5 should match product_ids in ticket , reason_id. ticket needs have criteria of answer or more not less.

below explanation of how tables after show attempts have been far.

tickets_answers

id    name        message 1     answer 1    message sent 2     answer 2    message number 2 3     answer 3    arbitrary text 4     answer 4    text 

tickets_answers_options

id   answer_id    option_type    option_id 1    1            product_id     10 2    1            product_id     20 3    1            reason_id      5 4    2            product_id     10 5    2            reason_id      3 6    3            product_id     30 7    3            reason_id      6 

the query executed in php have freedom in building via code. pull data ticket php , try match answer. using example above, if ticket has products 10,20,30,40 , reason_id 5 should match answer_id 1. since has 3 matching entries: product_ids 10,20 , reason_id 5. i've tried using inner joins follows.

select     ta.id tickets_answers ta  inner join tickets_answers_options tao on tao.answer_id = ta.id , tao.option_type = 'reason_id' , tao.option_id = 5  inner join tickets_answers_options tao_1 on tao_1.answer_id = ta.id , tao_1.option_type = 'product_id' , tao_1.option_id = 10 inner join tickets_answers_options tao_2 on tao_2.answer_id = ta.id , tao_2.option_type = 'product_id' , tao_2.option_id = 20 inner join tickets_answers_options tao_3 on tao_3.answer_id = ta.id , tao_3.option_type = 'product_id' , tao_3.option_id = 30 inner join tickets_answers_options tao_4 on tao_4.answer_id = ta.id , tao_4.option_type = 'product_id' , tao_4.option_id = 40 

this query built in php based on ticket info because ticket has product_ids 10,20,30,40 , reason_id 5 query becomes long.

i keep trying match full options of ticket criteria answer feels i'm doing backwards or wrong somehow. i've tried incorporating left joins query try , filter null entries cannot seems match answer ticket.

i have tried other way around start of joining results based on tickets union , comparing options answers.

(select tp.ticketid,          tp.productid option_id,          'product_id' option_type,         tao.answer_id    tickets_products tp          left join tickets_answers_options tao                 on tao.option_type = 'product_id'                    , tao.option_id = tp.productid    answer_id not null          , strict = 0 group option_id)  union  (select t.id,          t.reasonid,          'reason_id',          tao.answer_id    tickets t          left join tickets_answers_options tao                 on tao.option_type = 'reason_id'                    , tao.option_id = t.reasonid    answer_id not null          , strict = 0)  order  ticketid desc,            answer_id desc  

this gives me list multiple ticket_ids multiple answer_ids such:

ticketid    option_id   option_type    answer_id 60          12          reason_id       7 60          12          reason_id       6 59          12          reason_id       7 59          12          reason_id       6 58          12          reason_id       7 58          12          reason_id       6 57          10195       product_id      7 57          10199       product_id      7 57          10197       product_id      6 

what i'm trying result set find out how many times answer_id occurs ticketid. instance count on answer_id ticketid = 60 tickets in result set.

these have been 2 approaches of trying match answers tickets. hope of can shed light if i'm using right approach or if possible @ all. understand explanation or request might seem bit off since i'm not expert on mysql far. please let me know if need supply more information or edit in here.

thank time in advance.