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 make it 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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
