Union

Using UNION in MySql

[Alternative Future]

Hi all,

I have been successfully using the UNION function with MySql locally, but when deploying it onto my host’s server it complains with the following error:
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near '(SELECT *, column_name = 0 from table_name WHERE column_name LIKE '%keyword1%' ORDER BY ' at line 1

Here is the UNION query:
(SELECT *, column_name = 0 from table_name WHERE column_name LIKE '%keyword1%' ORDER BY column_name) UNION (SELECT *, column_name = 1 from table_name WHERE column_name LIKE '%keyword2%' AND column_name NOT LIKE '%keyword1%' ORDER BY column_name) UNION (SELECT *, column_name = 2 from table_name WHERE column_name LIKE '%keyword3%' AND column_name NOT LIKE '%keyword2%' ORDER BY column_name)

Can anyone suggest what might be going wrong? The host is using the latest version of MySql, same as I am using locally!

Any pointers are gratefully appreciated.

-George

[Alternative Future]

Anyone...?

The only difference I can see between the one I have locally and the one that is live, is, column_name is a primary key on the hosts server!

-Gs

[Alternative Future]

Would the fact that column_name is a primary be causing this problem?

-George

[f00sion]

are you unioning the same table together 3 times?

[Alternative Future]

HI f00sion,

>>are you unioning the same table together 3 times?

Yeah same table three queries on the same column.

The purpose is, when the user searches for an item with three keywords, I want to return all matching results for each keyword and in order of user input.

i.e. user enters into text box 'blue widget company' mySql query should return all results containing 'blue' then search again for 'widget' and finally search for company. My results would show in order all items with blue in their title - then - all items with widget - get the idea?

-George

[Alternative Future]

>>are you unioning the same table together 3 times?

Why do you think this might cause the problem?

-George

[txbakers]

why use union? You can use select * from table where keyword like '%widget%' or keyword like '%blue%' or keyword like '%large%'

It's not the most effiecient query, but you don't need unions.

ALSO, union query was only supported in the 4.0+ version of mySQL. It could be that you host has an older version.

[Alternative Future]

Thanks txbakers,

Just tried that one out and it works fine.
Is there a way to order the results in order of best matching i.e. first returned would be 'blue widgets' second returned would be 'blue handles' third 'yellow widgets'?
Or would I have to do this ORDER with my Java class file and set the order of the array of objects returned?

>>ALSO, union query was only supported in the 4.0+ version of mySQL. It could be that you host has an older version

Had a sneaky feeling this might be the case, tried contacting my hosts (now expect a delay of a week or so lol)

Once again thanks...

-George

[txbakers]

You can only order by fields that are in your select, so the example you gave would only need a simple "order by description" at the end.

However, if you wanted to order by Blue Handles, yellow Handles, blue JumpRopes (the second word) you couldn't do that in a basic order by unless the color and the item were in different fields.

Which is why I tend to split my DB fields down as far as possible, just in case I might want to sort one day.

So, "name" is split into "first", "last", address into "city, state, zip" etc.

There are advanced techniques for ordering, selecting etc. such as order by LEFT(field,2,4) DESC which will order on the field descending , but starting at the second character for the next four characters. (check syntax here...)