title: Inner join only when records to join
body: <p>I have two tables i want to join. I want to inner join records by specific date (CreateDate). What i want is to not take records when nothing could be joined. I join by Name field. For particural day (CreateDate) there should be 1:1 record by Name in both tables. If not i do not want to take stand alone record from either first or second table. Below examples to make a view.</p>
  
  <pre><code>for all tables:
  Id - PK/AI
  Name - varchar
  FieldX/FieldY - varchar
  CreateDate - Datetime 
  </code></pre>
  
  <p><strong>Table1</strong></p>
  
  <pre><code>ID | Name | FieldX | CreateDate
  </code></pre>
  
  <p><strong>Table2</strong></p>
  
  <pre><code>ID | Name | FieldY | CreateDate
  </code></pre>
  
  <p><strong>Table3</strong></p>
  
  <pre><code>ID | Name | FieldX | FIeldY | CreateDate
  </code></pre>
  
  <p><strong>Example:</strong></p>
  
  <p><strong>Table1:</strong></p>
  
  <pre><code>1  John  value10  2017-09-03
  2  Lili  value20  2017-09-03
  3  John  value30  2017-10-12
  4  Lili  value40  2017-10-12
  </code></pre>
  
  <p><strong>Table2:</strong></p>
  
  <pre><code>1  John  value50  2017-09-03
  2  Lili  value60  2017-09-03
  3  John  value70  2017-10-12
  4  Lili  value80  2017-04-01
  </code></pre>
  
  <p>Now when saying i want to join for day <code>2017-09-03</code> this should be final result in </p>
  
  <p><strong>Table3:</strong></p>
  
  <pre><code>John  value10  value50  2017-09-03
  Lili  value20  value60  2017-09-03
  </code></pre>
  
  <p><strong>For day: 2017-10-12</strong>
  Note that no record for Lili in Table2 for that day means no records to be taken (and vice versa if wouldn't be in Table1 and was in Table2 same situation - i do not want to take record)</p>
  
  <p><strong>Table3:</strong></p>
  
  <pre><code>John  value30  value70  2017-10-12
  </code></pre>
  
  <p><strong>What i have so far is this:</strong></p>
  
  <pre><code>INSERT INTO Table3 (Name, FieldX, FieldY, CreateDate)
  SELECT T1.Name, T1.FieldX, T2.FieldY, T1.CreateDate FROM Table1 As T1
  INNER JOIN Table2 As T2 ON T1.Name=T2.Name
  WHERE T1.CreateDate = '2017-10-12'
  </code></pre>
  
  <p>However something is wrong i do not get correct results. Can anyone help me out what i am missing?</p>