CODEWITHSUNDEEP
sqlpostgresqljoinpostgresql-9.3
Abraham P
Abraham P

Reputation: 15471

Why does the Inner Join query not work while multi where clause does

Lets say I run a crooked car company. Let's say I have the following table: car_engine_mileage_counters which is a join table from car_engines onto mileage_counters also storing a calculated field of mileage Lets also say that I encode a coefficient at the engine block level in my factory on an engine template.

UPDATE car_engine_mileage_counters
SET mileage = mileage_counters.mileage * coefficients.coefficient
FROM car_engines
INNER JOIN engine_templates
ON car_engines.template_id = engine_templates.id
INNER JOIN mileage_counters
ON mileage_counters.id = car_engine_mileage_counters.mileage_counter_id
INNER JOIN mileage_counter_templates
ON mileage_counter.template_id = mileage_counter_templates.id
INNER JOIN coefficients
ON coefficients.mileage_counter_template_id = mileage_counter_templates.id
WHERE coefficients.engine_template_id = engine_template.id AND car_engines.id = car_engine_mileage_counters.engine_id;

This (clearly fictitious) example fails with:

ERROR: invalid reference to FROM-clause entry for table "car_engine_mileage_counters" LINE 7: ON mileage_counters.id = car_engine_mileage_counters... ^ HINT: There is an entry for table "measure_instances_question_instances", but it cannot be referenced from this part of the query.

Enumerating all tables in a single FROM clause, and using WHERE AND in place of all INNER JOINs however works fine.

My question is, why? What is wrong with the inner join query? How can I fix it? Does it matter?

Upvotes: 1

Views: 86

Answers (1)

Khurram Ali
Khurram Ali

Reputation: 1679

UPDATE cem
SET mileage = mileage_counters.mileage * coefficients.coefficient
FROM car_engine_mileage_counters cem
INNER JOIN car_engines 
ON car_engines.id = cem.engine_id
INNER JOIN engine_templates
ON car_engines.template_id = engine_templates.id
INNER JOIN mileage_counters
ON mileage_counters.id = cem.mileage_counter_id
INNER JOIN mileage_counter_templates
ON mileage_counter.template_id = mileage_counter_templates.id
INNER JOIN coefficients
ON coefficients.mileage_counter_template_id = mileage_counter_templates.id
WHERE coefficients.engine_template_id = engine_template.id AND car_engines.id = cem.engine_id;

Upvotes: 2

Related Questions