title: DAO involving a JOIN query
body: <p>I am new to the world of DAO. I have 3 tables.</p>
  
  <ul>
  <li>Consumer</li>
  <li>Bill</li>
  <li>Receipt</li>
  </ul>
  
  <p><strong>Consumer</strong> table contains following fields</p>
  
  <ul>
  <li>consumer_id</li>
  <li>consumer_name</li>
  <li>address</li>
  </ul>
  
  <p><strong>Bill</strong> table contains</p>
  
  <ul>
  <li>bill_number</li>
  <li>bill_date</li>
  <li>consumer_id</li>
  <li>bill_amount</li>
  </ul>
  
  <p><strong>Receipt</strong> table contains</p>
  
  <ul>
  <li>receipt_no</li>
  <li>pay_date</li>
  <li>paid_amount</li>
  <li>consumer_id</li>
  </ul>
  
  <p><strong>Bill</strong> table has a <strong><em>many to one</em></strong> relationship with <strong>Consumer</strong> table,  <strong>Receipt</strong> table also has a <strong><em>many to one</em></strong> relationship with <strong>Consumer</strong> table.</p>
  
  <p>Currently I have created three classes</p>
  
  <ol>
  <li>Consumer</li>
  <li>Bill</li>
  <li>Receipt</li>
  </ol>
  
  <p>and created DAOs for them like ConsumerDAO, BillDAO, ReceiptDAO. They contains basic CRUD operations.</p>
  
  <p>Now I want a listing of a consumer involving data from these three tables. I am using a JOIN SQL query for the same. It is as follows:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT c.consumer_id,
         c.consumer_name,
         c.address,
         r.receipt_no,
         r.pay_date,
         r.paid_amount,
         b.bill_date,
         b.bill_number,
         b.bill_amount
  FROM consumer c
  LEFT OUTER JOIN
    (SELECT r.consumer_id,
            r.pay_date,
            r.receipt_number,
            r.paid_amount,
            ROW_NUMBER() OVER (PARTITION BY r.consumer_id
                               ORDER BY r.pay_date) AS rank
     FROM receipt r) r ON (c.consumer_id = r.consumer_id
                           AND r.rank = 1)
  LEFT OUTER JOIN
    (SELECT b.consumer_id,
            b.bill_number,
            b.bill_date,
            b.bill_amount,
            ROW_NUMBER() OVER (PARTITION BY b.consumer_id
                               ORDER BY b.bill_date) AS rank
     FROM bill b) b ON (c.consumer_id = b.consumer_id
                        AND b.rank = 1)";
  </code></pre>
  
  <p>I was wondering in which DAO I should place this query ? I thought to add a <strong><em>Bill field</em></strong> and a <strong><em>Receipt field</em></strong> in the <strong>Consumer</strong> class and add a <strong><em>getCurrentBillAndReceipt()</em></strong> method in the <strong>ConsumerDAO</strong>. Is it the right way to do ?</p>
  
  <p>I read the following question:</p>
  
  <p><a href="https://stackoverflow.com/questions/10793915/how-to-create-a-dao-for-join-tables">how to create a DAO for join tables</a></p>
  
  <p>and some more, but I was not able to get the idea.</p>