title: Remove NULL values from multiple LEFT JOIN in sql server
body: <p>I have the following tables</p>
  
  <p><strong>ITEM1</strong></p>
  
  <pre><code>ID | NAME | GEARS | ITEM2_ID  |
  -------------------------------
  1  | Test | 56    | 4         |
  2  | Test2| 12    | 2         |
  </code></pre>
  
  <p><strong>ITEM3</strong></p>
  
  <pre><code>ID | NAME | DATA  | ITEM2_ID  |
  -------------------------------
  1  | Test | 1     | 1         |
  2  | Test7| 22    | 3         |
  </code></pre>
  
  <p><strong>ITEM2</strong></p>
  
  <pre><code>ID |   VALUE       |
  --------------------
  1  |   is simple   |  
  2  |   is hard     | 
  3  |   is different|
  4  |   is good     |
  5  |   very good   |
  </code></pre>
  
  <p>And my query</p>
  
  <pre><code>SELECT TOP(3) * FROM (
      SELECT ID, 
         rankTable.RANK as RANK_,
          TOTALROWS = COUNT(*) OVER() 
   FROM ITEM2
   INNER JOIN 
       CONTAINSTABLE(ITEM2, [VALUE], 'ISABOUT("good")') as rankTable
   ON ITEM2.ID = rankTable.[KEY]
  ) as ITEM2table
  
  LEFT JOIN (
      SELECT ID, 
        NAME, 
        GEARS, 
        ITEM2_ID 
      FROM ITEM1
  ) as ITEM1table
  ON ITEM1table.ITEM2_ID = ITEM2table.ID
  
  LEFT JOIN (
      SELECT ID, 
          NAME, 
          DATA, 
          ITEM2_ID 
      FROM ITEM3
  ) as ITEM3table
  ON ITEM3table.ITEM2_ID = ITEM2table.ID
  </code></pre>
  
  <p>and the results</p>
  
  <p><img src="https://i.stack.imgur.com/Wzwfl.png" alt="enter image description here"></p>
  
  <p>How to remove (if is possible) the first row (ID = 5) using the above SQL query ? Also I want to show TOTALROWS = 1 because other row contains NULL's except first 3 columns.</p>
  
  <p>Thank you.</p>