title: Subquery or a double query?
body: <p>For a client I have built a travelagency website. Now they asked me to optimize and automize some things. One of these things is that the accommodations shown in the searchlist must be bookable.</p>
  
  <p>Simplified this is the structure</p>
  
  <p>I have an AccommodationObject, an Accommodation has 1 or more PricePeriods.</p>
  
  <p>What I need to do pick out the last priceperiod connected to a given accommodation. The priceperiod has a field 'date_until', which is a timestamp. In the query a check must be made, current timestamp must be less than the last timestamp from a priceperiod.</p>
  
  <p>This is easy to do with a second query which just gets the last timestamp from the priceperiod table from all the priceperiod-rows connected to the given accommodation.</p>
  
  <p>I'm wondering if this is the best case, or if I should use a subquery for this. And if so, how would a query like this look? I don't have much experience with subqueries.</p>
  
  <h3>Update</h3>
  
  <p>Table structure (simple)</p>
  
  <p>Accommodation ->ID</p>
  
  <p>PricePeriod -> ID | AccommodationID | DateUntil</p>
  
  <p>Simplified:</p>
  
  <pre><code>SELECT fieldlist FROM Accommodation WHERE ID = $id
  SELECT MAX(DateUntil) FROM PricePeriod WHERE AccommodationID = $id
  </code></pre>
  
  <p>But I would like to this in one query.  I hope it's clear this way..</p>