471,092 Members | 1,561 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Subqueries in MySQL 4.0?

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?
Jul 20 '05 #1
3 2074
Noozer wrote:
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?


How about something like:

SELECT DISTINCT CategoryID, Category from tblCategory
WHERE tblCategory.CategoryID IN
(
(SELECT Category from tblEntries) UNION
(SELECT Category2 from tblEntries) UNION
(SELECT Category3 from tblEntries)
)

DISCLAIMER: This is untested!

HTH,
-rick-
Jul 20 '05 #2
> How about something like:

SELECT DISTINCT CategoryID, Category from tblCategory
WHERE tblCategory.CategoryID IN
(
(SELECT Category from tblEntries) UNION
(SELECT Category2 from tblEntries) UNION
(SELECT Category3 from tblEntries)
)
Doh!! Completely forgot about IN... Thanks!
DISCLAIMER: This is untested!


I should know soon!
Jul 20 '05 #3
Noozer wrote:
How can I make this work in MySQL 4.0?


Subqueries are not supported in MySQL 4.0.

Does the following do what you want?

SELECT DISTINCT A.CategoryID, A.Category
FROM tblCategory AS A INNER JOIN tblEntries AS B
ON A.CategoryID IN (B.Category, B.Category2, B.Category3);

Regards,
Bill K.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Emanuele D'Arrigo | last post: by
6 posts views Thread by pete | last post: by
6 posts views Thread by Daniel Elliott | last post: by
3 posts views Thread by compu_global_hyper_mega_net_2 | last post: by
2 posts views Thread by Kevin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.