title: SQL Server: how to combine Join and Where clause for this case?
body: <p>I have a table engines (eball), a table with damages (dv) and a table with installed service product (sp). Each engine may have multiple damages and multiple service products.</p>
  
  <p>Now I want to create a query with all damages, its engine information and if a service product "Retrofit" was installed, the information of the service product Retrofit.</p>
  
  <p>My SQL below is wrong, as I do not get the damages with a different service product installed. If I do not include this where clause I will get a row (equals damage) for each service product installed of the same damage???</p>
  
  <pre><code>SELECT *
  FROM
  dbo.EPI_EB2S_damage_report_v  as dv 
  LEFT OUTER JOIN dbo.EPI_all_v  as eball ON dv.DB_NO = eball.DB_NO
  LEFT OUTER JOIN dbo.ServiceProducts sp ON dv.DB_NO = sp.DB_NO
  WHERE (sp.SERVICE_PRODUCT_NAME = 'Retrofit Pulse' OR sp.SERVICE_PRODUCT_NAME is NULL OR sp.SERVICE_PRODUCT_NAME = '')
  </code></pre>