MySQL UNION trouble

timbr8ks

This is a MySQL database question. I have a table of classes (school classes) which gets archived occasionally. So, I need to search both the active table and the archived table for a specific class. My query looks like this:

( SELECT * FROM class WHERE class_id = '22' ) 
UNION 
( SELECT * FROM class_archive WHERE class_id = '22' )

Both SELECT statements work indpendently, but the UNION of the two does not. I get:

1064 - You have an error in your SQL near 
'( SELECT * FROM class WHERE class_id = '22' ) UNION 
( SELECT * FROM class_ar' at line1

I'm running MySQL 5.0.37 on Windows XP. I can't see anything wrong with this query.

NogDog

Have you tried it without the parentheses around the queries?

timbr8ks

The parentheses are a required part of the query structure. I did just try it, however, and the error is right at the word "UNION."

NogDog

Strictly speaking (upon further reading on my part), the parens are only required in certain circumstances, but should not hurt anything if they are not required. And since you've already stated you're not using a MySQL version older than 4.0 and that you've tested the individual queries, you've pretty much reached the limits of my knowledge. 🙁

(My guess is you're going to find out it's some really small and seemingly trivial thing, since "real" problems should return more meaningful - and useful - error messages.)

leatherback

Just a silly question.. You do use double quotes around the query right..? Otherwise the single quotes will through the interpreter off.

When you run the query in phpadmin, do you get a better error message?

timbr8ks

Silly questions are certainly appreciated, as we've all made silly mistakes. I can't get this query to run even from a MySQL command line. Again, both parts will work, but the full query will not. Is this a bug with UNION?

NogDog

timbr8ks;10880099 wrote:
Silly questions are certainly appreciated, as we've all made silly mistakes. I can't get this query to run even from a MySQL command line. Again, both parts will work, but the full query will not. Is this a bug with UNION?

I copied-and-pasted your query into a mysql window on my pc, substituting table and column names for valid one in my DB, and it worked fine. I'm running MySQL 5.0.51.

(SELECT * FROM products WHERE products_id = '2')
UNION
(SELECT * FROM products WHERE products_id = '3')

timbr8ks

Thanks for the feedback. Your query, however, is searching the same table. I'm not sure if that's the issue.

NogDog

timbr8ks;10880105 wrote:
Thanks for the feedback. Your query, however, is searching the same table. I'm not sure if that's the issue.

I needed to ensure that each query returned the same number of columns while using the like you did. I also tried it using different tables, but listing explicit column names in each query and it worked OK, too, e.g.:

(SELECT products_id, description FROM products WHERE products_id = '2')
UNION
(SELECT * FROM skills WHERE skills_id = '3')