title: select statement for a field inside another select statement - oracle
body: <p>I have a select statement written for a table i.e STAY table in which 2 of the date fields namely ADMSN_DT and DSCHRG_DT are not availabe. So I was informed to fetch these fields from another table named CLM table by joining the claim_id.</p>
  
  <p>So I have framed my select query as,</p>
  
  <pre><code>select 
  Member_id as MemberID,
  (select a.ADMSN_DT from CLM a, STAY b where a.cl_id = b.claim_id) as Admission_Date,
  (select a.DSCHRG_DT from CLM a, STAY b where a.cl_id = b.claim_id) as Discharge_Date,
  field 1,
  feild 2, such as ... some fields from STAY table
  from STAY;
  </code></pre>
  
  <p>The problem here is I got a 2 records from this table, the claim_id is different but I get the same date in both the record.</p>
  
  <p>For Eg:-</p>
  
  <pre><code>Claim_id    Admission_date   Discharge_date
  1234         11/12/2015        12/12/2015          -- this claim id has dates in clm table
  5678         11/12/2015        12/12/2015          -- this claim id has no dates in clm table
  </code></pre>
  
  <p>its just copying the same value from first record to the second record.. HOw to avoid this? Any help is much appreciated.</p>
  
  <p>Thanks!     </p>