title: Selecting Distinct Fields From Joined Rows
body: <p>I have a table <code>ANIMAL</code> and another table <code>CALF_PARENT</code></p>
  
  <p>Design of <code>ANIMAL</code> table is:</p>
  
  <pre><code>ID PK IDENTITY(1,1),
  TagNo varchar(30)
  
  and other columns...
  </code></pre>
  
  <p>Design of <code>CALF_PARENT</code> is:</p>
  
  <pre><code>ID PK IDENTITY(1,1),
  Parent int (FK from Animal),
  IsMother varchar(1)
  </code></pre>
  
  <p>I am writing following query to join two tables:</p>
  
  <pre><code>SELECT a.[TagNo]
    ,a.ID   
   ,a2.TagNo
  ,isnull(cp.Parent,0)   
   ,a3.TagNo 
  ,isnull(cp.Parent,0)    
  
  FROM [dbo].[ANIMAL] a
  
    LEFT JOIN [CALF_PARENT] cp
    ON a.ID = cp.Calf
  
    LEFT JOIN ANIMAL a2
    ON a2.ID = cp.Parent AND cp.IsMother = 'Y' AND a2.ID IS NOT NULL
  
    LEFT JOIN ANIMAL a3
    ON a3.ID = cp.Parent AND cp.IsMother = 'N' AND a3.ID IS NOT NULL
  </code></pre>
  
  <p>Its returning me record like this:</p>
  
  <pre><code>Tag, CalfID, FatherTagNo, FatherID, MotherTagNo, MotherID
  
  FA-56   21   AB-670         3          
  FA-56   21                            CW-59         7   
  </code></pre>
  
  <p>I want it to return me single row like this:</p>
  
  <pre><code>Tag, CalfID, FatherTagNo, FatherID, MotherTagNo, MotherID
  
  FA-56   21   AB-670         3          CW-59         7  
  </code></pre>
  
  <p>What would be the simplest way to implement it.</p>