Trying to get the following SQL to execute in MySQL 4.0...
SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory AS A
INNER JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblEntries )
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblEntries )
UNION SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblEntires ) ) As B
ON A.CategoryID = B.Cat;
Basically, tblEntries has three different Category columns. The UNION
creates table B which is simply a single column containing all the entries
for the three Category columns of tblEntries.
From this I want to display a list of Category descriptions for all the
categories used in the tblEntries, so I pull the text description from the
tblCategories table.
How can I make this work in MySQL 4.0?
Also, as an aside, is the above the most efficient way to write this query?
I figure that the UNION should return DISTINCT items, but I don't see where
to place the DISTINCT keyword... If I put it in each UNION, won't there
still be the possibility of getting three of each, since there are three
columns involved?
