title: pulling columns from two tables when there is a group by
body: <p>I have a query which returns the custref having multiple tetranumbers(1 to n mapping)</p>
  
  <pre><code>select *
    from cdsheader
   where custref in(select custref
                      from(select *
                             from cdsheader h,custandaddr c
                            where h.custref = c.cwdocid
                              and c.addresstype = 'C')
                     group by custref
                    having count(distinct( tetranumber )) &gt; 1) 
  </code></pre>
  
  <p>count 5144</p>
  
  <p>My objective is to pull the matching address details along with the above result, but I guess I am missing something here.</p>
  
  <p>Something like...</p>
  
  <pre><code>select a.cworderid,a.cwcreated,a.organisationtype,a.custref,a.tetranumber,
         b.buildingname,b.streetname,b.posttown,b.postcode,b.country
    from cdsheader a,custandaddr b
   where custref in (select custref
                       from cdsheader h,custandaddr c
                      where h.custref = c.cwdocid
                        and c.addresstype = 'C')
                      group by custref
                     having count(distinct( tetranumber )) &gt; 1)
   order by a.custref,a.tetranumber,a.cworderid; 
  </code></pre>