dimanche 4 octobre 2015

MySQL JOIN on non-unique Value with an IF condition

I have an attendees tables that looks like this:

event_id    user_id
  1           16
  1           12
  1           13
  2           14
  2           16
  2           12
  3           11
  3           16

and information about events in a table like this:

event_id    eventcenter_id      date_begin                date_end
    1             2         2016-10-31 18:00:00        2016-10-31 21:00:00
    2             1         2016-11-20 18:00:00        2016-11-20 18:40:00
    3             2         2016-11-20 15:00:00        2016-11-20 18:00:00

and finally a table that looks like this:

eventcenter_id              name
      1              Fire Hall
      2              Sunny Lake 

I need a query that returns all events being attended by a user. the result should return the event center name, date_begin and date_end sh i can use these objects in a calendar. When I try to join on the event_id from on the attendees table it gives the error not unique value on event_id. the result I'm after should look like this:

user_id eventCenter_name    date_begin                    date_end
   16        sunny lake    2016-10-31 18:00:00         2016-10-31 21:00:00
   16        fire hall     2016-11-20 18:00:00         2016-11-20 18:40:00
   16        sunny lake    2016-11-20 15:00:00         2016-11-20 18:00:00

Aucun commentaire:

Enregistrer un commentaire