Andrew Alsup wrote:
Is it possible to rewrite this as a top-level join, without using a subquery?
Something along the lines of:
SELECT em.end_item_meter_id
FROM end_item_meter em
inner join Meter_Reading mr
on em.end_Item_meter_id = mr.end_item_meter_id
INNER JOIN End_Item_Event eie
ON eie.end_Item_Event_Id = mr.end_Item_event_Id
INNER JOIN End_Item_Inventory eii
ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id
WHERE
eii.End_Item_Inventory_Id = 6061799
Does that query produce the right results for you?
Thanks for the suggestion. That does get the correct results for the
SELECT. However, I really need this in an UPDATE statement.
UPDATE end_item_meter
SET end_item_id = ?
WHERE end_item_meter_id IN (... subquery ...)
I re-wrote it as a SELECT for testing and determined that it SELECT
with subquery had the same performance issue as UPDATE with subquery.
I don't think its possible to perform a JOIN on the subject table of
an update statement. Thats why I've used subqueries in the past.
You can try to rewrite the query to use EXISTS instead of IN. I'm not
sure if Derby already does this transformation for your query, but you
can try to print the query execution plan to check.
Try if this gives you the correct answer:
SELECT em.end_item_meter_id
FROM end_item_meter em
WHERE EXISTS
(
SELECT 1
FROM
Meter_Reading mr
INNER JOIN End_Item_Event eie
ON eie.end_Item_Event_Id = mr.end_Item_event_Id
INNER JOIN End_Item_Inventory eii
ON eii.end_Item_inventory_Id = eie.end_Item_inventory_id
WHERE
mr.end_item_meter_id=em.end_Item_meter_id and
eii.End_Item_Inventory_Id = 6061799
);
--
Jørgen Løland