search multiple tables with same data

[beerguzler]

Hi,

I have been racking my brains on this .

I have 3 tables where Product col is the same across all 3.

Tables
id /Product / Price

So far I have been using
$query = "Select `product`, `price` as p, 'tbl1' as n from `tbl1` where `product` REGEXP '".$trimmed."'
UNION ALL
Select `product`, `price` as p, 'tbl2' as n from `tbl2` where `product` REGEXP '".$trimmed."'
UNION ALL
Select `product`, `price` as p, 'tbl3' as n from `tbl3` where `product` REGEXP '".$trimmed."' ORDER by `name`";

Output gives me
Product | vendor | Price

Wanting to do a search where product is combine from all 3 tables and prices in different colums - >output

Product | Price (tbl1) | Price (tbl2) | Price (tbl3)

Any idea's ?

[guelphdad]

you would use your scripting language like php or PERL to format your results in that manner.

[beerguzler]

Ye , I actually have the output via PHP through a loop.

My thinking was to get the query right then have php do the rest. The more I think about it , maybe setting up a Temp table to reduce the load....

Any thoughts moving forward?

The php script merely is echo the colums...

[guelphdad]

why do you have three tables and not one in the first place?

[beerguzler]

The aim is to have the ability to combine for comparison of prices .

So I can look up a product and see the 3 different prices from vendors.