Hello:
I am trying to write a select statement that will select over several tables. I have eight tables with the same table structure. I am using MySQL.
The reason for the same table structure is that each table is a different category of product. I originally had all eight tables as one table but the table was too large to work with at times as it totaled over 300 million rows.
The search is customizable so not all eight tables will be searched at the same time during every search.
The problem is that when all eight tables are searched using the "UNION ALL" feature, it is not displaying the same results as if all eight tables were one.
If I change the order of the tables within the series of union statements I will get different results. An example would be if I change table one and table threes position i will get different results when compared to the results if the tables are in order from one to eight in the union statement. Below is a syntax example if I am unclear:
SELECT name, etc...
FROM cat1
UNION ALL
SELECT name, etc...
FROM cat2
UNION ALL
SELECT nname, etc...
FROM cat3
UNION ALL
SELECT name, etc...
FROM cat4
LIMIT 50
The following code will produce different results
SELECT name, etc...
FROM cat3
UNION ALL
SELECT name, etc...
FROM cat2
UNION ALL
SELECT name, etc...
FROM cat1
UNION ALL
SELECT name, etc...
FROM cat4
LIMIT 50
What I would like to do is display the same results no matter what the table order and base the results off of the closest match to the users search string which is based on the 'name' column.
I have been reading the following reference regarding union's and ordering results but I have not been able to get it to work: "
http://dev.mysql.com/doc/refman/4.1/en/union.html"
I do not know how to order the results by name so I will get the same results no matter what order the union is done, and also limit the final results to 50 rows.
Can anyone suggest how this can be done, other than combining all the tables into one?
Thanks.
Start Free Trial
