Problem with multiple JOINS to the same table

[butlins]

I've got a problem with a site I'm developing. I have a table for press releases which has three drop down menus so that they can store up to three themes with each press release, stored as news.Theme1, news.Theme2 and news.Theme3. They're all populated from the same lookup table, called 'themes'.

I didn't want to create a joining table if I could avoid it (even though I know that's the best way to normalise the data), as they only wanted a maximum of three themes, so I thought this would save me writing so editing pages in their CMS.

If I wanted to write a JOIN to get one of them out in a recordset, I'd do something like:

SELECT news.newsNo, news.newsDate, news.newsTitle, themes.themeName
FROM news
LEFT JOIN themes on news.newsTheme1 = themes.themeNo
ORDER BY newsDate DESC

which works fine.

However, I want to pull out all three themes. I can't find a way to save the results of the JOIN with a different name, to make the themes.themeName record pulled out by the SELECT statement unique.

SELECT news.newsNo, news.newsDate, news.newsTitle, themes.themeName
FROM news
LEFT JOIN themes on news.newsTheme1 = themes.themeNo
LEFT JOIN themes on news.newsTheme2 = themes.themeNo
LEFT JOIN themes on news.newsTheme3 = themes.themeNo
ORDER BY newsDate DESC

Obviously doesn't work

I'm fairly new at this, so I'd be very grateful for any suggestions.

[guelphdad]

you need to give a table an alias each time you self join to it.

[butlins]

Thanks - I was starting to use UNION to append all the bits together, but this gives me another angle to try too.