SQL Server: how to combine Join and Where clause for this case?

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.

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.

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???

SELECT *
FROM
dbo.EPI_EB2S_damage_report_v  as dv 
LEFT OUTER JOIN dbo.EPI_all_v  as eball O开发者_StackOverflow中文版N 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 = '')

---

It looks so me like you need something along the lines of:

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 AND sp.SERVICE_PRODUCT_NAME = 'Retrofit Pulse'