title: coalesce with subquery in a where clause in Oracle
body: <p>Hy,</p>
  
  <p>I have the next sql query in Oracle: </p>
  
  <p>Imagine I have a table "items" with "id" and "name" fields and other table "prices_items" which have three fields named "id, itemId, category". The category may have three values: "1,2,3". So the query I need to do is get the price of an item from the table "prices_items" but the item can have until three prices because of the category field. So, in priotiry order I need to get the price of an item which has category 1, if the item doesnt have this category I have to find the price for category 2 and so on.</p>
  
  <pre><code>from items 
  left join prices_items on prices_items.itemId = items.itemId 
  where prices_items.id = coalesce(select id 
                                   from prices_items 
                                   where itemId= items.itemId and category=1,
                                   select id 
                                   from prices_items 
                                   where itemId= items.itemId and category=2, 
                                   select id 
                                   from prices_items 
                                   where itemId= items.itemId and category=3) 
  </code></pre>
  
  <p>The query I am using is like this but I dont know how its working because coalesce is being executed on each join?. How is this being executed?</p>
  
  <p>Thanks</p>