help with query

[mottwsc]

I have two tables:

 

Events
  EventType                               VARCHAR(10)
  InterestID1                             INT
  InterestID2                             INT
  InterestID3                             INT

Interests
  InterestID                              INT
  InterestDescription                     VARCHAR(20)

 

I'm trying to query both tables so that, for each row in Events, I can get all fields from Events and the corresponding interestDescription from Interests for each of the three interests (InterestID1, ID2 and ID3). 

 

This would output something like:

EventType  InterestID1  InterestDescription (for ID1)  InterestID2  InterestDescription (for ID2)  InterestID3  InterestDescription (for ID3)

Car Show  3  Engines  6  Interiors  17  Fuel Efficiency

 

However, I can't do this with a query like:

SELECT *, i.interestDescription FROM EVENTS e
JOIN Interests i on 
(e.interestID1 = i.interestID or e.interestID2 = i.interestID or e.interestID3 = i.interestID)

 

Can anyone suggest how to do this?

 

Thanks!

[mikosiko]

JOIN the Interest table 3 times (different aliases)

[mottwsc]

Would you mind showing an example of how to properly do this?

[mottwsc]

I got it.  Thanks for your suggestion.

[fenway]

I got it.  Thanks for your suggestion.

Then post it for everyone to see.

[mottwsc]

SELECT e.*

    , i1.interestDescription AS i1_interestDescription

    , i2.interestDescription AS i2_interestDescription

    , i3.interestDescription AS i3_interestDescription

  FROM EVENTS e

LEFT OUTER

  JOIN Interests AS i1

    ON i1.interestID = e.interestID1

LEFT OUTER

  JOIN Interests AS i2

    ON i2.interestID = e.interestID2

LEFT OUTER

  JOIN Interests AS i3

    ON i3.interestID = e.interestID3