sql - MySQL check if between multiple criteria with unique identifier -

i have list of events.

| table: events | event_id | event_location | event_date | |----------|----------------|------------| | 10       | denver         | 2014-02-01 | * | 11       | chicago        | 2014-04-01 | * | 12       | denver         | 2014-06-01 |  | 13       | seattle        | 2014-08-01 | * | 14       | chicago        | 2014-10-01 | | 15       | denver         | 2014-11-01 | * 

i have list of location access, dated.

| table: allowed | allowed_location | date_begin | date_end   | |------------------|------------|------------| | denver           | 2014-01-01 | 2014-03-01 | | chicago          | 2014-03-01 | 2014-05-01 | | seattle          | 2014-07-01 | 2014-09-01 | | denver           | 2014-10-01 | 2014-12-01 | 

what want find of event_id have event_date between 1 of respective allowed_location rows (date_begin , date_end).

the results first table meet criteria denoted asterisks above.

i able results, not seeking, following query:

select event.event_id event left join   (select allowed_location,           date_begin,           date_end    allowed) allowed on allowed.allowed_location=event.event_location (event.event_date >= allowed.date_begin)   , (event.event_date <= allowed.date_end) 

this give me results, left joins 1 of results allowed table. cannot wrap head around way check if date event between of allowed date ranges location.

any appreciated.

a simple join return row matching each row combination allowed on condition. there, need filter out non-matching dates. (also note between operator, makes range-based comparison simpler.)

select e.event_id events e     join allowed on a.allowed_location = e.event_location e.event_date between a.date_begin , a.date_end 

this returns result expect.