title: SQL notes getting joined twice because they are related to service not unit
body: <p>Ok I have a database that looks as follows:
  Orders, Services, OrderUnits, Notes</p>
  
  <pre><code>Orders: id
  Services: id, orderid
  Units: id, Orderid, unitnumber
  Notes: id, relatedid, text, date
  </code></pre>
  
  <ul>
  <li>Orders have 1 to many order units</li>
  <li>Orders have 1 to many services</li>
  <li>Services have 1 to many notes</li>
  </ul>
  
  <p>I am trying to relate only the notes that relate to the unit and show those records, the problem is there is no database relation between notes and unit, only service and note.</p>
  
  <p>I am trying to get the following:</p>
  
  <pre><code>select u.unitnumber
        ,n.date
        ,o.id
        ,s.id
        ,n.text
   FROM tblorders o
   INNER JOIN tblServices s on o.id = s.orderid
   INNER JOIN tblUnits u on o.id = u.orderid
   INNER JOIN tblNotes n on s.id = n.RelatedId
   WHERE n.Text LIKE '&lt;p&gt;The status for the Unit # % has changed from % to OUTTOVENDOR&lt;/p&gt;'
  </code></pre>
  
  <p>The problem here is that this gives me the 2 units 4 times if there is 2 units in the same service because the service is related to the note and not the unit. </p>
  
  <p>The note does contain the unit number in it so I was wondering if I could do anything there to somehow relate the note to the unit number?</p>
  
  <p>Thanks!</p>