This is the structure of the tables:
Table1
product_code - product_price
100001 - 100
Table2
product_code - product_price
100001 - 200
Table3
product_code - product_price
100001 - 300
Table4
product_code - product_price
100001 - 400
Table5
product_code - product_price
100001 - 500
Table6
product_code - product_price
100001 - 600
This query is fine apparently
$price = DB::queryFirstRow("
select * from Table1, Table2, Table3, Table4, Table5, Table6
where Table1.product_code = Table2.product_code AND Table1.product_code = 100001");
the price of the last table according to the order within the db
echo $price['product_price']; // 600 the price of the last table according to the order within the db
I try to order by lowest price to get 100 result
$price = DB::queryFirstRow("
select * from Table1, Table2, Table3, Table4, Table5, Table6
where Table1.product_code = Table2.product_code AND Table1.product_code = 100001 ORDER BY product_price");
I get this error
// ERROR: Column 'product_price' in order clause is ambiguous
Any ideas how to solve it, thank you very much in advance for all your help!
First, when you use SELECT * mysql will load all fields of all tables in from and joins.
Same name field in MySQL tables overwrite each other, then mysql will return only the field of last table (it's Table6 in you query).
if you do that query:
SELECT * FROM `Table1` WHERE `product_code` = 100001
UNION SELECT * FROM `Table2` WHERE `product_code` = 100001
UNION SELECT * FROM `Table3` WHERE `product_code` = 100001
UNION SELECT * FROM `Table4` WHERE `product_code` = 100001
UNION SELECT * FROM `Table5` WHERE `product_code` = 100001
UNION SELECT * FROM `Table5` WHERE `product_code` = 100001
Mysql will return (not exact in that order):
product_code - product_price
100001 - 100
100001 - 200
100001 - 300
100001 - 400
100001 - 500
100001 - 600
You can add a ORDER BY product_price ASC LIMIT 1; or DESC if you want.
But do it only if you are studing, on a real application same name in fields of diferent tables is unrecommended.