query using multiple tables

[StreamActive]

Hi ya ,

I'm stuck on this query which I cannot seem to get my head around.

I have 5 tables , each containing the following format

tbl 1
id ¦ Product ¦ Price
1 candy 3.50
2 candy 100 5.00

table 2
id ¦ Product ¦ Price
1 candy 4.00
2 candy 100 5.50

table 3
id ¦ Product ¦ Price
1 candy 3.67
2 candy 100 6.00

etc ..

I'm trying to create a search query that will display the various products and prices based on the search.

So the output would be

table 1 ¦ Candy ¦ 3.50
table 3 ¦ candy ¦ 3.67
table 2 ¦ candy ¦ 4.00
table 1 ¦ candy 100 ¦ 5.00
table 2 ¦ candy 100 ¦ 5.50
table 3 ¦ candy 100 ¦ 6.00

I have played around with union using the following query, obviously $trimmed is my search variable.

"Select `product`,`price` as p, 'table1' as n from `table1` where `product` REGEXP '".$trimmed."'
UNION ALL
Select `product`,`price` as p, 'table2' as n from `table2` where `product` REGEXP '".$trimmed."'
UNION ALL
Select `product`,`price` as p, 'table3' as n from `table3` where `product` REGEXP '".$trimmed."'
ORDER by `product`";

Any thoughts on how I can work this out, I will be provided with further tables in the future however getting 3 working is a challange.

Cheers

[magicstar]

what was your result when you ran your query?

[StreamActive]

the result I get is

table 1 ¦ Candy ¦ 3.50
table 2 ¦ candy ¦ 4.00
table 2 ¦ candy 100 ¦ 5.50
table 3 ¦ candy 100 ¦ 6.00

At first i thought it would merely an Order within mysql yet when I compare the raw data it seems to differ.

One thing I forgot to add is the ability to have the table's in seperate colums so it would be the following

Each table has its own colum, would this be posible as I'm also trying to include the as an option.

table 1 ¦ ¦ Candy ¦ 3.50
¦ table 2 ¦ candy ¦ 4.00
¦ table 2 ¦ candy 100 ¦ 5.50
¦table 3 ¦ candy 100 ¦ 6.00

Amy suggestions? each table contains about 1000+ records , so any alternative solutions are welcome...