How to get parts from table part and not exist on both tables chemical missing and chemical master?

Transact-SQL
, ,
1

I work on sql server 2012 i need to get parts from table part and not exist on both tables chemical missing and

chemical master by using exist or not exist and if there are more ways please help me

I need to rewrite these statement with another statement using

not exist or exists and if there are any way I need it

SELECT  np.PartNumber, np.CompanyID
FROM parts.nop_part np
INNER JOIN Z2DataCompanyManagement.CompanyManagers.Company c WITH(NOLOCK) ON np.CompanyID = c.CompanyID
LEFT  JOIN parts.chemicalmaster cm ON cm.PartID = np.PartID
LEFT  JOIN Parts.ChemicalMissingParts cmp ON cmp.PartID = np.PartID
WHERE cm.ChemicalID IS NULL AND cmp.ChemicalMissingPartID IS NULL

so How to rewrite by using exist or not exist and if there are other way tell me ?

2

The query you wrote should work, but may return more rows than you need. I don't know why you JOINed to the Company table, you don't need it based on the conditions and columns you're selecting.

A NOT EXISTS version would be:

SELECT  np.PartNumber, np.CompanyID
FROM parts.nop_part np
WHERE NOT EXISTS(SELECT 1 FROM parts.chemicalmaster cm WHERE cm.PartID = np.PartID)
AND NOT EXISTS(SELECT 1 FROM Parts.ChemicalMissingParts cmp WHERE cmp.PartID = np.PartID)
1 Like
3

thank you solved