title: REST API Architecture: How to Represent Joined Tables
body: <h1>Question</h1>
  <p>I have a complex query that joins three tables and returns a set of rows with each row having data from it's sibling tables. How is it possible to represent this in a RESTful way?</p>
  <p>FWIW I know there is not necessarily a &quot;right&quot; way to do it, but I'm interested in learning about what might be the most extensible and durable solution for this situation.</p>
  <h1>Background</h1>
  <p>In the past I've represented single tables that more or less mirror the literal structure of the url. For example, the url <code>GET /agents/1/policies</code> would result in a query like <code>select * from policies where agent_id = 1</code>.</p>
  <h1>Assumption</h1>
  <p>It seems like the url doesn't necessarily have to be so tightly coupled to the structure of the database layer. For example, if the complex query was something like:</p>
  <pre><code>select
    agent.name as agent_name,
    policy.status as policy_status,
    vehicle.year as vehicle_year
  from
    policies as policy
    join agents as agent on policy.agent_id = agent.id
    join vehicles as vehicle on vehicle.policy_id = policy.id
  where 1=1
    and policy.status = 'active';
  
  # outputs something like:
  { &quot;agent_name&quot;: &quot;steve&quot;, &quot;policy_status&quot;: &quot;single&quot;, &quot;vehicle_year&quot;: &quot;1999&quot; }
  </code></pre>
  <p>I could represent this QUERY as a url instead of TABLES as url. The url for this could be <code>/vehicles</code>, and if someone were to want to query it (with id or some other parameter like <code>/vehicles?vehicle_color=red</code>) I could just pass that value into a prepared statement.</p>
  <h1>Bonus Questions</h1>
  <ul>
  <li>Is this an antipattern?</li>
  <li>Should my queries always be run against EXISTING tables instead of prepared statements?</li>
  </ul>
  <p>Thanks for your help!</p>