sql - Select Products/Events in Multiple Categories using MySQL -

as example, need query find events categorised :

either category id 1, 2 or 3 (let's these running, swimming, cycling) , category id 15 (let's 5km distance)

it equally extended 15 or 16 perhaps (5km or 10km)

i have 3 tables : event, event_event_categories , event_categories

event_event_catgories designed you'd expect primary key each related table:

event_id, category_id --------------------- 2345      1 2456      2 7893      3 2345      15 

event_categories designed follows parent category

category_id, category_name, parent_category_name ------------------------------------------------ 1            running        sport 2            swimming       sport 3            cycling        sport ... 15           5km            distance 16           10km           distance 

what need events in plain english:

(running or swimming or cycling) , (5km)

the distinction , made parent category different , can handle outside of mysql if necessary.

in example above, event no. 2345 should returned query.

i've got query far i'm unsure how proceed.

select distinct e.event_name, e.address_town, e.address_county, eo.event_organiser_name, count(distinct ec.id) catcount event e  inner join event_event_categories ecm on e.id = ecm.event_id inner join event_category ec on ec.id = ecm.event_category_id inner join event_organiser eo on e.event_organiser_id = eo.id ec.id in (1,2,3,15) , e.start_date_time >= '2016-01-01' , e.start_date_time <= '2016-12-31' , e.enabled = true group ecm.event_id order catcount desc, e.start_date_time; 

i hope i've explained enough , hear , suggestions.

you can having clause:

select e.event_name, e.address_town, e.address_county, eo.event_organiser_name event e inner join      event_event_categories ecm      on e.id = ecm.event_id inner join      event_category ec      on ec.id = ecm.event_category_id inner join      event_organiser eo      on e.event_organiser_id = eo.id ec.id in (1, 2, 3, 15) ,       e.start_date_time >= '2016-01-01' ,        e.start_date_time <= '2016-12-31' ,       e.enabled = true group ecm.event_id having sum(ec.id in (1, 2, 3)) > 0 ,        sum(ec.id = 15) > 0; 

this chooses events @ least 1 of "1", "2", or "3". if want 1 match these, use:

having sum(ec.id in (1, 2, 3)) = 1 ,        sum(ec.id = 15) > 0; 

the use of having clause type of query powerful. call type of query "set-within-set" because looking @ sets of categories within events.