MySQL subquery question

I am trying to accomplish something and havent been able to find out if it can be done. I have a table "Events" that has event info. Within that table are ID's for Associations that sponsor that event. Sometimes it is only one and sometimes it can be as many as five. These Associations are in the "Associations" table. Within the Assoc开发者_Go百科iations table, there are several details about that Association. What I am trying to do is do a query that will search the events table, and get all of the events that are between now and the event date, as well as retrieve the information about each Association that relates to each particular event. Here is the query that I have so far:

SELECT
`Events`.EventID,
`Events`.AssociationID,
`Events`.Association2ID,
`Events`.Association3ID,
`Events`.Association4ID,
`Events`.Association5ID,
`Events`.DateFrom,
`Events`.DateTo,
`Events`.EventName,
`Events`.VenueID,
`Events`.TestnTune,
`Events`.ShownShine,
`Events`.SpecialInfo,
`Events`.OtherInfo,
`Events`.Rating,
`Events`.EventOverlay,
`Events`.HavePictures,
`Events`.IncludeInSchedule,
`Events`.EventURL,
Associations.Active,
Associations.Acronym,
Associations.OrgName,
Associations.WebURL,
Associations.LogoURL,
Associations.AssociationID,
Venues.LocationName,
Venues.Location,
Venues.longetude,
Venues.latitude,
Venues.Directions,
Venues.SitePros,
Venues.SiteCons,
Venues.BasicInfo,
Venues.SiteRating,
Venues.HostedEvents,
Venues.CurrentWeather
FROM
`Events`
LEFT JOIN Associations ON `Events`.AssociationID = Associations.AssociationID AND `Events`.Association2ID = Associations.AssociationID AND `Events`.Association3ID = Associations.AssociationID AND`Events`.Association4ID = Associations.AssociationID AND `Events`.Association5ID = Associations.AssociationID
LEFT JOIN Venues ON `Events`.VenueID = Venues.VenueID
WHERE
`Events`.DateFrom >= NOW()

It looks like you're trying to model an many-to-many relationship by using several 1-n relationships and naming them association1, association2, etc... That's probably not a good idea.

Create a new entity called EventToAssociation (or similar), holding foreign keys to both events, and association. Then, your joins will be a lot easier to create.

In an example query, this would read:

SELECT *
FROM Event e
JOIN EventToAssociation e2a ON e.EventID = e2a.EventID
JOIN Association a ON e2a.AssociationID = a.AssociationID

If changing the schema is not an option, then you'll have to join Association several times to Event, e.g.

SELECT *
FROM Event e
LEFT OUTER JOIN Associations a1 ON e.AssociationID = a1.AssociationID
LEFT OUTER JOIN Associations a2 ON e.Association2ID = a2.AssociationID
LEFT OUTER JOIN Associations a3 ON e.Association3ID = a3.AssociationID
LEFT OUTER JOIN Associations a4 ON e.Association4ID = a4.AssociationID
LEFT OUTER JOIN Associations a5 ON e.Association5ID = a5.AssociationID

Every join you make is between your Events-Table and the same Associations-Table. This is just like an AND-Condition.

Try using Aliases:

LEFT JOIN Associations Asso1 ON `Events`.AssociationID = Asso1.AssociationID
LEFT JOIN Associations Asso2 ON `Events`.Association2ID = Asso2.AssociationID