开发者

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'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜