title: How to retrieve data from mutiple tables using common column
body: <p>I am trying to get the data from the both tables by joining on the common column. But it's giving me the duplicates. </p>
  
  <pre><code> ;WITH LocID AS
      (
      SELECT 1 AS LocID, 1 AS CommonID 
      UNION ALL 
      SELECT 2 AS LocID, 1 AS CommonID 
      ), 
  
      LocAddress AS(
      SELECT 456 AS AddressID, 'Address-1'AS 'Address',  'City-1' AS City, 'State-1' AS 'State', 1 AS CommonID
      UNION ALL 
      SELECT 789 AS AddressID, 'Address-2'AS 'Address',  'City-2' AS City, 'State-2' AS 'State', 1 AS CommonID
      )
  
          SELECT L.LocID, A.Address, A.City, A.State FROM LocID L INNER JOIN LocAddress A ON L.CommonID = A.CommonID ;
  </code></pre>
  
  <p>The desired output would have only two rows as show below. </p>
  
  <p><strong>LocID  Address   City    State</strong></p>
  
  <p>1     Address-1 City-1  State-1</p>
  
  <p>2     Address-2 City-2  State-2   </p>
  
  <p>Is there any way I can get this?</p>