On 22 Jan 2010, at 7:59, Yan Cheng Cheok wrote:
> SELECT measurement_type.value, measurement.value, measurement_unit.value
> FROM
> measurement_type INNER JOIN
> (measurement_unit INNER JOIN
> (measurement INNER JOIN
> (lot INNER JOIN unit ON (lot_id = fk_lot_id) WHERE lot_id = 7)
> ON (fk_unit_id = unit_id))
> ON (fk_measurement_unit_id = measurement_unit_id))
> ON (fk_measurement_type_id = measurement_type_id);
Yeah, you can't use WHERE in the middle of an expression. You can just add your extra constraint to the ON-clause here
though:ON (lot_id = fk_lot_id AND lot_id = 7)
You don't need to nest all those joins btw, and it doesn't help to read your query. I turned it inside out into this to
makeit a bit more readable, I hope I got it right:
SELECT measurement_type.value, measurement.value, measurement_unit.value
FROM lot INNER JOIN unit ON (lot_id = fk_lot_id)
INNER JOIN measurement ON (fk_unit_id = unit_id)
INNER JOIN measurement_unit ON (fk_measurement_unit_id = measurement_unit_id)
INNER JOIN measurement_type ON (fk_measurement_type_id = measurement_type_id)
WHERE lot_id = 7
Since you don't seem to need any data from lot in your results you can remove that table from your query like this:
SELECT measurement_type.value, measurement.value, measurement_unit.value
FROM measurement ON (fk_unit_id = unit_id)
INNER JOIN measurement_unit ON (fk_measurement_unit_id = measurement_unit_id)
INNER JOIN measurement_type ON (fk_measurement_type_id = measurement_type_id)
WHERE fk_lot_id = 7
Notice that we now use a different column in the WHERE clause, namely measurement.fk_lot_id instead of lot.lot_id.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4b5995db10606279919623!
