title: Join 3 tables and return unique/distinct rows
body: <p>I have 3 tables in SQL Server Database.<br>
  I want to fetch the <strong><code>distinct/unique</code></strong> rows When I join these 3 tables.</p>
  
  <p>For this I am using <code>distinct</code> keyword after <code>SELECT</code> query but its not working.<br>
  It still return repeated records in <code>SELECT</code> query (i.e., <code>ID, Column1, Column2</code> all records are repeating since duplicate records already exist in 1 of the 3 tables)</p>
  
  <p>Please let me know how to join 3 tables and return <code>unique/distinct</code> rows.<br>
  Please reply</p>
  
  <p><strong>Edit:</strong> Below is my current query along with result set:</p>
  
  <p><a href="https://i.stack.imgur.com/0zaCI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0zaCI.png" alt="enter image description here"></a></p>
  
  <p>Please check the returned result. It has duplicate records in it.</p>
  
  <p><strong>Edit 2:</strong>
  Really sorry for above confusion. Please check this section:</p>
  
  <p>If suppose one of the Join table gives me below result set (Ignore Yellow text for now):
  <a href="https://i.stack.imgur.com/8o0H6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8o0H6.png" alt="enter image description here"></a></p>
  
  <p><strong>Requirement:</strong></p>
  
  <p>Now, I want to SELECT records in below format from above result set:</p>
  
  <ul>
  <li>If any RequestID (exaple: ABC123)/BarcodeNo (example: B1) has Action
  = Save &amp; Submit together then only <code>Action = Submit</code> record/row shall display in SELECT query.</li>
  <li>If any RequestID (example: XYZ678)/BarcodeNo (example: B22) has
  Action = Save &amp; Submit &amp; Resubmit (multiple Resubmit) together, etc
  then only <code>Action = Latest Resubmit</code> (among multiple Resubmit)
  record/row shall display in SELECT query.</li>
  <li>If any RequestID (example: GHI987)/BarcodeNo (example: B3) has Action
  = Submit &amp; Resubmit (multile Resubmit) together, etc then only <code>Action = Latest Resubmit</code> (among multiple Resubmit) record/row shall display in SELECT query.</li>
  <li>If any RequestID (example: PQR902)/BarcodeNo (example: B44) has
  Action = Submit &amp; Resubmit together, etc then only <code>Action = Resubmit</code>
  record/row shall display in SELECT query.</li>
  <li>If any RequestID (example: TTT878)/BarcodeNo (example: B5) has Action
  = Submit alone, etc then only <code>Action = Submit</code> record/row shall display in SELECT query.</li>
  </ul>
  
  <p>Above "etc" means, Actions <em>other than</em> 'Save' , 'Submit' , 'Resubmit'</p>
  
  <p>To fulfill above requirement (Yellow Text, based on above requirement), I tried below query:<br/></p>
  
  <pre><code>Select distinct R.RequestID, R.BarcodeNo, W.Action, W.CreatedDate 
   INTO #TempTable
  
   From [Sunway_AP].[Invoice].[tbl_Request] (NOLOCK) R
   Left Join [Sunway_AP].[Invoice].[tbl_Xml](NOLOCK) X On X.XmlID = R.XmlID
   Left Join [Sunway_AP].[Invoice].[tbl_WorkflowHistory] (NOLOCK) W On W.RequestID = R.RequestID
   order by R.RequestID
  
  SELECT * FROM 
  (
      SELECT distinct * FROM (SELECT distinct *, ROW_NUMBER() OVER(PARTITION BY RequestID ORDER BY CreatedDate desc) RowNumber FROM #TempTable 
      WHERE [Action] = 'Resubmit') AS t
      WHERE RowNumber = 1
  
      UNION ALL 
  
      SELECT distinct * FROM (SELECT distinct *, ROW_NUMBER() OVER(PARTITION BY RequestID ORDER BY CreatedDate desc) RowNumber FROM #TempTable
      WHERE [Action] = 'Save' 
      AND RequestID NOT IN (SELECT DISTINCT RequestID FROM #TempTable WHERE [action] = 'Submit')
      AND RequestID NOT IN (SELECT DISTINCT RequestID FROM #TempTable WHERE [action] = 'Resubmit')) AS t
      WHERE RowNumber = 1
  
      UNION ALL
  
      SELECT distinct * FROM (SELECT distinct *, ROW_NUMBER() OVER(PARTITION BY RequestID ORDER BY CreatedDate desc) RowNumber FROM #TempTable
      WHERE [Action] = 'Submit' 
      AND RequestID NOT IN (SELECT DISTINCT RequestID FROM #TempTable WHERE [action] = 'Resubmit')) AS t
      WHERE RowNumber = 1
  ) AS a 
  order by RequestID
  </code></pre>
  
  <p>but it does not give me expected result.
  Please let me know what shall I change in my query to get the desired output ?</p>