title: Compare rows from two join tables and gives result from two tables in alternate fashion
body: <pre><code>Table X
  
  First Name    Last Name    Job
  ----------    ---------    ---
  CHIN          JACK         A
  GREEN         JAMES        B
  
  Table Y
  
  First Name    Last Name    Job
  ----------    ---------    ---
  CHIN          JACK         D
  GREEN         JAMES        B
  
  
    RESULT
  ------
  First Name          Last Name           Job
  ----------          ---------           ---
  CHIN                JACK                &gt;&gt; A &lt;&lt;   --(first row from Table X)
  CHIN                JACK                &gt;&gt; D &lt;&lt;   --(first row from Table Y)
  &gt;&gt; CHIN &lt;&lt;          &gt;&gt; JACK &lt;&lt;          &gt;&gt; A &lt;&lt;   --(first row from Table X)
  &gt;&gt; GREEN &lt;&lt;         &gt;&gt; JAMES &lt;&lt;         &gt;&gt; B &lt;&lt;   --(second row from Table Y)
  &gt;&gt; GREEN &lt;&lt;         &gt;&gt; JAMES &lt;&lt;         &gt;&gt; B &lt;&lt;   --(second row from Table X)
  &gt;&gt; CHIN &lt;&lt;          &gt;&gt; JACK &lt;&lt;          &gt;&gt; D &lt;&lt;   --(first row from Table Y)
  GREEN               JAMES               B         --(second row from Table X) 
  GREEN               JAMES               B         --(second row from Table Y)
  </code></pre>
  
  <p>I am trying to compare rows from two tables on a column by column basis. If there is a mismatch in column values between two tables I highlight it by appending '>> &lt;&lt;' for the mismatch field in both tables TableX and TableY in the result table. Result table has row from TableX and TableY alternately. Please see the result table to get a better idea on my expected result.</p>
  
  <p>Last two tables of the result set is optional. I am okay with having it or not having it.</p>
  
  <p>This query is what I initially thought of but it doesn't give rows from two tables alternately and it takes into account only column values in TableX
  Is there a possible way to write a query to achieve the result table. Please advise.</p>
  
  <pre><code>SELECT
  CASE
    WHEN x.firstname = y.firstname THEN x.firstname
    ELSE '&gt;&gt; '+x.firstname+' &lt;&lt;'
  END FirstName,
  CASE 
    WHEN x.lastname = y.lastname THEN x.lastname
    ELSE '&gt;&gt; '+x.lastname+' &lt;&lt;'
  END LastName,
  CASE 
    WHEN x.job = y.job THEN x.job
    ELSE '&gt;&gt; '+x.job+' &lt;&lt;'
  END Job
  FROM
  TableX x
  JOIN
  TableY y
  ON x.firstname &lt;&gt; y.firstname
  OR x.lastname &lt;&gt; y.lastname
  OR x.job &lt;&gt; y.job
  </code></pre>
  
  <p>Improvement
  I figured out a query that very much resembles my expected result. I achieved it by adding a new column 'Company' who values is always the same in TableX and TableY. So now the table looks like</p>
  
  <p>Table X</p>
  
  <pre><code>Company    First Name    Last Name    Job
  -------    ----------    ---------    ---
  C          CHIN          JACK         A
  C          GREEN         JAMES        B
  </code></pre>
  
  <p>Table Y</p>
  
  <pre><code>Company    First Name    Last Name    Job
  -------    ----------    ---------    ---
  T          CHIN          JACK         D
  T          GREEN         JAMES        B
  </code></pre>
  
  <p>And this is my new query</p>
  
  <pre><code>    SELECT
      *
  FROM 
  (
  SELECT      ROW_NUMBER() OVER (ORDER BY x.Company) as RowNumber,
              1 AS 'RowOrder',
              x.TableName,
              CASE
                  WHEN x.firstname = y.firstname THEN x.firstname
                  ELSE '&gt;&gt; '+x.firstname+' &lt;&lt;'
              END FirstName,
              CASE
                  WHEN x.LastName = y.LastName THEN x.LastName
                  ELSE '&gt;&gt; '+x.LastName+' &lt;&lt;'
              END LastName,
              CASE
                  WHEN x.Job = y.Job THEN x.Job
                  ELSE '&gt;&gt; '+x.Job+' &lt;&lt;'
              END Job
      FROM
              #tableX x
          JOIN #tableY y
          ON x.FirstName &lt;&gt; y.FirstName
          OR x.LastName &lt;&gt; y.LastName
          OR x.Job &lt;&gt; y.Job
  
  UNION ALL
  
  SELECT 
              ROW_NUMBER() OVER (ORDER BY y.Company) as RowNumber,
              2 AS 'RowOrder',
              y.TableName,
              CASE
                  WHEN x.firstname = y.firstname THEN y.firstname
                  ELSE '&gt;&gt; '+y.firstname+' &lt;&lt;'
              END FirstName,
              CASE
                  WHEN x.LastName = y.LastName THEN y.LastName
                  ELSE '&gt;&gt; '+y.LastName+' &lt;&lt;'
              END LastName,
              CASE
                  WHEN x.Job = y.Job THEN y.Job
                  ELSE '&gt;&gt; '+y.Job+' &lt;&lt;'
              END Job
      FROM
              #tableX x
          JOIN #tableY y
          ON x.FirstName &lt;&gt; y.FirstName
          OR x.LastName &lt;&gt; y.LastName
          OR x.Job &lt;&gt; y.Job
  
  ) rt
  ORDER BY rt.RowNumber, rt.RowOrder
  </code></pre>
  
  <p>Now the problem is that the join in the second select statement inside braces scrambles the order in which the rows are displayed. This results in wrong ordering of rows in the final result. Please find the final result below. You can also find the individual select statements inside the braces. The last table in the picture gives the rows in scrambled order which messes up the final result.</p>
  
  <p><a href="https://i.stack.imgur.com/k8jIM.png" rel="nofollow noreferrer">Final result, result from first select statement, result from second select statement</a></p>